shlist

commit 5717aff22f71a051239d0297ffe6ab0ae5f3b886
parent 2f685d0dbb321a3def8cd1489bb8db2d072adbb3
Author: kyle <kyle@0x30.net>
Date:   Sat, 23 Jan 2016 09:51:49 -0700

sl: make sql slightly smarter and do less work in code

- get all list member phone numbers back in a single query

Diffstat:
M
server/sl
 | 
21
+++++++++------------

1 file changed, 9 insertions(+), 12 deletions(-)
diff --git a/server/sl b/server/sl
@@ -468,18 +468,11 @@ sub msg_lists_get {
 
 		$log->print("found list '$list_num':'$list_name'\n");
 
-		# Find all members of this list
-		my @members;
-		$db->{get_list_members}->execute($list_num);
-		while (my ($member_num) = $db->{get_list_members}->fetchrow_array()) {
-
-			# Don't re look-up our own number
-			if ($member_num eq $dev->{num}) {
-				push @members, $dev->{phnum};
-				next;
-			}
-			push @members, devnum_to_phnum($db, $member_num);
-		}
+		$db->{list_members_phnums}->execute($list_num);
+		my $members_ref = $db->{list_members_phnums}->fetchall_arrayref();
+
+		# $members_ref comes back as an array of arrays, flatten it
+		my @members = map {@$_} @$members_ref;
 
 		my $list = {
 			num => $list_num,
@@ -827,6 +820,10 @@ sub prepare_stmt_handles {
 		lists.num = list_members.list and list_members.device = ?};
 	$self->{get_lists} = $dbh->prepare($sql);
 
+	$sql = qq{select devices.phone_num from devices, list_members
+		where devices.num = list_members.device and list_members.list = ?};
+	$self->{list_members_phnums} = $dbh->prepare($sql);
+
 	# list_members table queries
 	$sql = 'select device from list_members where list = ?';
 	$self->{get_list_members} = $dbh->prepare($sql);