578
587
def _doSearch(self, text="", vocab_filter=None):
579
588
"""Return the people/teams whose fti or email address match :text:"""
589
if self.enhanced_picker_enabled:
590
return self._doSearchWithImprovedSorting(text, vocab_filter)
592
return self._doSearchWithOriginalSorting(text, vocab_filter)
594
def _doSearchWithOriginalSorting(self, text="", vocab_filter=None):
595
private_query, private_tables = self._privateTeamQueryAndTables()
597
extra_clauses = [self.extra_clause]
599
extra_clauses.extend(vocab_filter.filter_terms)
601
# Short circuit if there is no search text - all valid people and
602
# teams have been requested. We still honour the vocab filter.
606
Join(self.cache_table_name,
607
SQL("%s.id = Person.id" % self.cache_table_name)),
609
tables.extend(private_tables)
610
result = self.store.using(*tables).find(
613
Or(Person.visibility == PersonVisibility.PUBLIC,
616
Person.merged == None,
621
# Do a full search based on the text given.
623
# The queries are broken up into several steps for efficiency.
624
# The public person and team searches do not need to join with the
625
# TeamParticipation table, which is very expensive. The search
626
# for private teams does need that table but the number of private
627
# teams is very small so the cost is not great.
629
# First search for public persons and teams that match the text.
632
LeftJoin(EmailAddress, EmailAddress.person == Person.id),
635
# Create an inner query that will match public persons and teams
636
# that have the search text in the fti, at the start of the email
637
# address, or as their full IRC nickname.
638
# Since we may be eliminating results with the limit to improve
639
# performance, we sort by the rank, so that we will always get
640
# the best results. The fti rank will be between 0 and 1.
641
# Note we use lower() instead of the non-standard ILIKE because
642
# ILIKE doesn't hit the indexes.
643
# The '%%' is necessary because storm variable substitution
644
# converts it to '%'.
645
public_inner_textual_select = SQL("""
647
SELECT Person.id, 100 AS rank
651
SELECT Person.id, rank(fti, ftq(?))
653
WHERE Person.fti @@ ftq(?)
655
SELECT Person.id, 10 AS rank
657
WHERE IrcId.person = Person.id
658
AND lower(IrcId.nickname) = ?
660
SELECT Person.id, 1 AS rank
661
FROM Person, EmailAddress
662
WHERE EmailAddress.person = Person.id
663
AND lower(email) LIKE ? || '%%'
664
AND EmailAddress.status IN (?, ?)
668
""", (text, text, text, text, text,
669
EmailAddressStatus.VALIDATED.value,
670
EmailAddressStatus.PREFERRED.value,
673
public_result = self.store.using(*public_tables).find(
676
Person.id.is_in(public_inner_textual_select),
677
Person.visibility == PersonVisibility.PUBLIC,
678
Person.merged == None,
679
Or( # A valid person-or-team is either a team...
680
# Note: 'Not' due to Bug 244768.
681
Not(Person.teamowner == None),
682
# Or a person who has a preferred email address.
683
EmailAddress.status == EmailAddressStatus.PREFERRED),
685
# The public query doesn't need to be ordered as it will be done
687
public_result.order_by()
689
# Next search for the private teams.
690
private_query, private_tables = self._privateTeamQueryAndTables()
691
private_tables = [Person] + private_tables
693
# Searching for private teams that match can be easier since we
694
# are only interested in teams. Teams can have email addresses
695
# but we're electing to ignore them here.
696
private_result = self.store.using(*private_tables).find(
699
SQL('Person.fti @@ ftq(?)', [text]),
704
private_result.order_by(SQL('rank(fti, ftq(?)) DESC', [text]))
705
private_result.config(limit=self.LIMIT)
707
combined_result = public_result.union(private_result)
708
# Eliminate default ordering.
709
combined_result.order_by()
710
# XXX: BradCrittenden 2009-04-26 bug=217644: The use of Alias and
711
# _get_select() is a work-around for .count() not working
712
# with the 'distinct' option.
713
subselect = Alias(combined_result._get_select(), 'Person')
714
exact_match = (Person.name == text)
715
result = self.store.using(subselect).find(
716
(Person, exact_match),
718
# XXX: BradCrittenden 2009-05-07 bug=373228: A bug in Storm prevents
719
# setting the 'distinct' and 'limit' options in a single call to
720
# .config(). The work-around is to split them up. Note the limit has
721
# to be after the call to 'order_by' for this work-around to be
723
result.config(distinct=True)
724
if exact_match is not None:
725
# A DISTINCT requires that the sort parameters appear in the
726
# select, but it will break the vocabulary if it returns a list of
727
# tuples instead of a list of Person objects, so we create
728
# another subselect to sort after the DISTINCT is done.
729
distinct_subselect = Alias(result._get_select(), 'Person')
730
result = self.store.using(distinct_subselect).find(Person)
732
Desc(exact_match), Person.displayname, Person.name)
734
result.order_by(Person.displayname, Person.name)
735
result.config(limit=self.LIMIT)
738
def _doSearchWithImprovedSorting(self, text="", vocab_filter=None):
739
"""Return the people/teams whose fti or email address match :text:"""
581
741
private_query, private_tables = self._privateTeamQueryAndTables()
582
742
extra_clauses = [self.extra_clause]
added
removed
lib/lp/registry/vocabularies.py
