def search_user_checkpoints(user_obj, search_term):
    """
    Searches both my UserCheckpoints and my friends' with a literal search term.
    Searches a Checkpoint's name, description, and User's (Facebook) name (union)
    """
    from db import UserCheckpoint, db, FacebookUser, User, FriendConnection, Checkpoint
    search_term_with_wildcard = "%"+search_term+"%"
    
    #query that contain my own and my friends' usercheckpoints
    FriendUserCheckpoint, FriendFacebookUser, FriendUser = aliased(UserCheckpoint), aliased(FacebookUser), aliased(User)
    ucp = (db.session.query(UserCheckpoint).
           join(Checkpoint, Checkpoint.id == UserCheckpoint.checkpoint_id).
           join(FriendUser, FriendUser.id == UserCheckpoint.user_id).
           join(FriendFacebookUser, FriendFacebookUser.id == FriendUser.facebook_user_id).
           join(FriendConnection, FriendConnection.fb_user_to == FriendFacebookUser.id).
           join(FacebookUser, FacebookUser.id == FriendConnection.fb_user_from).
           join(User, User.facebook_user_id == FacebookUser.id).
           filter(or_(User.id == user_obj.id, FriendUser.id == user_obj.id)).
           filter(Checkpoint.demo == False)        
           )
    
    name_search = ucp.filter(Checkpoint.name.ilike(search_term_with_wildcard))
    desc_search = ucp.filter(Checkpoint.description.ilike(search_term_with_wildcard))
    fb_name_search = ucp.filter(FriendFacebookUser.name.ilike(search_term_with_wildcard))
    
    result = name_search.union(desc_search).union(fb_name_search)
    
    return result.all()

def unwanted_list(request):
    who = aliased(User, name="who")
    by = aliased(User, name="by")
    a = DBSession.query(UnWanted, who, by). \
        join(who, UnWanted.user).join(by, UnWanted.by_user). \
        all()
    a = obj_list_join(a, ['', 'user_', 'by_'])
    return {'unwanted': a}

    def rolesForRbacSQL(self, rbacId, sql=None):
        """
        @see: IRbacService.rolesForRbacSQL
        """
        child, parent = aliased(RoleNode), aliased(RoleNode)

        sql = sql or self.session().query(RoleMapped)
        sql = sql.join(child, child.role == RoleMapped.Id)
        sql = sql.join(parent, and_(child.left >= parent.left, child.right <= parent.right))
        sql = sql.join(RbacRole, and_(RbacRole.role == parent.role, RbacRole.rbac == rbacId))

        return sql

 def __init__(self, Acl, AclAccess, Compensate, signatures=None):
     '''
     Construct the compensate service alchemy.
     
     @param AclAccess: class of WithAclAccess
         The ACL access relation mapped class.
     @param Compensate: class of WithCompensate
         The compensate relation mapped class.
     @param signatures: dictionary{string: string|callable(identifier) -> string}
         A dictionary containing as keys the signatures that will be injected and as a value either the marker to be injected
         or a callable that takes the identifier as a parameter and provides the marker string value.
     '''
     assert isinstance(Acl, MappedSupport), 'Invalid mapped class %s' % Acl
     assert issubclass(AclAccess, WithAclAccess), 'Invalid acl access class %s' % AclAccess
     assert issubclass(Compensate, WithCompensate), 'Invalid compensate class %s' % Compensate
     if __debug__:
         if signatures is not None:
             assert isinstance(signatures, dict), 'Invalid fill in signatures %s' % signatures
             for signature, marker in signatures.items():
                 assert isinstance(signature, str), 'Invalid signature %s' % signature
                 assert isinstance(marker, str) or callable(marker), 'Invalid marker %s' % marker
     
     self.Acl = Acl
     self.AclIdentifier = modelId(Acl)
     self.AclAccess = AclAccess
     self.AliasAclAccess = aliased(AclAccess)
     self.Compensate = Compensate
     
     self.signatures = signatures

def date(date, latest=False):
    try:
        if isinstance(date, (str, unicode)):
            date = datetime.strptime(date, "%Y-%m-%d")

        if isinstance(date, datetime):
            date = date.date()

    except:
        abort(404)

    pilot_alias = aliased(User, name='pilot')

    columns = {
        0: (Flight, 'index_score'),
        1: (pilot_alias, 'name'),
        2: (Flight, 'olc_classic_distance'),
        3: (Airport, 'name'),
        4: (Club, 'name'),
        5: (AircraftModel, 'name'),
        6: (Flight, 'takeoff_time'),
        7: (Flight, 'id'),
        8: (Flight, 'num_comments'),
    }

    return _create_list(
        'latest' if latest else 'date',
        request.args, date=date, columns=columns)

 def rank(self):
     inner_team = aliased(Team)
     return (DBSession.query(func.count('*') + 1).
             select_from(inner_team).
             filter(inner_team.score > Team.score).
             correlate(Team).
             label('rank'))

    def date(self, date, **kw):
        try:
            if isinstance(date, str):
                date = datetime.strptime(date, "%Y-%m-%d")

            if isinstance(date, datetime):
                date = date.date()
        except:
            raise HTTPNotFound

        pilot_alias = aliased(User, name='pilot')

        columns = {
            0: (Flight, 'index_score'),
            1: (pilot_alias, 'display_name'),
            2: (Flight, 'olc_classic_distance'),
            3: (Airport, 'name'),
            4: (Club, 'name'),
            5: (AircraftModel, 'name'),
            6: (Flight, 'takeoff_time'),
            7: (Flight, 'id'),
            8: (Flight, 'num_comments'),
        }

        if kw.get('today', False):
            return self.__do_list('today', kw, date=date, columns=columns)
        else:
            return self.__do_list('date', kw, date=date, columns=columns)

    def getOriginalSource(self, source):
        originalSource = aliased(SourceMapped, name="original_source")

        sql = self.session().query(originalSource)
        sql = sql.join(SourceMapped, originalSource.URI == SourceMapped.OriginURI)
        sql = sql.filter(SourceMapped.Id == source)

        return sql.one().Id

    def get_data(self):
        logging.debug("These queries will take a few mins to run.")

        budget_query = self.session.query(
            self.models.MovieInfo.movie_id,
            func.max(
                cast(
                    func.replace(
                        func.replace(self.models.MovieInfo.info, ",", ""),
                        "$", ""),
                    types.Numeric)
            ).label('budget')
        ).filter(
            self.models.MovieInfo.movie_id.in_(self.movie_ids),
            self.models.MovieInfo.info_type_id.in_([BUDGET_TYPE_ID]),
            self.models.MovieInfo.info.like('$%'),
        ).group_by(self.models.MovieInfo.movie_id
        ).subquery()

        year_query = self.session.query(
            self.models.MovieInfo.movie_id,
            func.min(
                func.substr(self.models.MovieInfo.info, -4)
            ).label('release_year')
        ).filter(
            self.models.MovieInfo.movie_id.in_(self.movie_ids),
            self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]),
            self.models.MovieInfo.info.like('USA:%')
        ).group_by(self.models.MovieInfo.movie_id
        ).subquery()

        budget_alias = aliased(self.models.MovieInfo, budget_query)
        year_alias = aliased(self.models.MovieInfo, year_query)

        return self.session.query(
            budget_query.columns.movie_id,
            budget_query.columns.budget,
            year_query.columns.release_year
        ).join(
            year_alias, year_alias.movie_id == budget_alias.movie_id
        ).distinct(
            self.models.MovieInfo.movie_id
        ).filter(
            self.models.MovieInfo.movie_id.in_(self.movie_ids),
        )

 def __init__(self, db="", sql_logging=False, schema=None):
     if db == "" or db is None:
         # In-memory SQLite
         connect_url = "sqlite:///"
     if "://" in db:
         # User has provided a full path
         connect_url = db
     else:
         # Assume a SQLite file
         connect_url = "sqlite:///%s" % db
     engine = sqlalchemy.create_engine(connect_url, echo=sql_logging)
     self._orm = _Orm(engine, schema=schema)
     Session = sessionmaker(bind=engine)
     self._session = Session()
     self._stoptime1 = aliased(StopTime, name="first_stop_time")
     self._stoptime2 = aliased(StopTime, name="second_stop_time")
     self._transfer_fromstop = aliased(Stop, name="tr_from_stop")
     self._transfer_tostop = aliased(Stop, name="tr_to_stop")

    def rightsForRbacSQL(self, rbacId, sql=None):
        """
        @see: IRbacService.rightsForRbacSQL
        """
        child, parent = aliased(RoleNode), aliased(RoleNode)

        subq = sql or self.session().query(RightMapped)
        subq = subq.join(RbacRight, RbacRight.right == RightMapped.Id)
        subq = subq.join(child, child.role == RbacRight.rbac)
        subq = subq.join(parent, and_(child.left >= parent.left, child.right <= parent.right))
        subq = subq.join(RbacRole, and_(RbacRole.role == parent.role, RbacRole.rbac == rbacId))

        sql = sql or self.session().query(RightMapped)
        sql = sql.join(RbacRight, and_(RbacRight.right == RightMapped.Id, RbacRight.rbac == rbacId))

        sql = sql.union(subq).distinct(RightMapped.Id).order_by(RightMapped.Id)

        return sql

    def getUnpublishedBySource(self, sourceId, thumbSize=None, offset=None, limit=None, detailed=False, q=None):
        '''
        @see: IBlogPostService.getUnpublished
        '''
        assert q is None or isinstance(q, QBlogPostUnpublished), 'Invalid query %s' % q
        
        postVerification = aliased(PostVerificationMapped, name='post_verification_filter')
              
        sql = self.session().query(BlogPostMapped)
        sql = sql.filter(BlogPostMapped.Feed == sourceId)
        
        deleted = False
        if q:
            if QBlogPostUnpublished.isDeleted in q:
                deleted = q.isDeleted.value                
            sql = buildQuery(sql, q, BlogPostMapped)
        
        if q:
            if QWithCId.search in q:
                all = self._processLike(q.search.ilike) if q.search.ilike is not None else self._processLike(q.search.like)
                sql = sql.filter(or_(BlogPostMapped.Meta.ilike(all), BlogPostMapped.CreatedOn.ilike(all), \
                                     BlogPostMapped.Content.ilike(all), BlogPostMapped.ContentPlain.ilike(all), \
                                     ))
                
            if QWithCId.status in q or QWithCId.checker in q:
                sql = sql.join(postVerification, postVerification.Id == BlogPostMapped.Id)     
                sql = sql.join(VerificationStatusMapped, VerificationStatusMapped.id == postVerification.statusId) 
                
            if QWithCId.status in q: 
                sql = sql.filter(VerificationStatusMapped.Key == q.status.equal) 
                
            if QWithCId.checker in q: 
                sql = sql.filter(postVerification.Checker == q.checker.equal)
                
            if (QWithCId.cId not in q) or (QWithCId.cId in q and QWithCId.cId.start not in q \
               and QWithCId.cId.end not in q and QWithCId.cId.since not in q and QWithCId.cId.until not in q):
                
                sql = sql.filter(BlogPostMapped.PublishedOn == None) 
                if deleted: sql = sql.filter(BlogPostMapped.DeletedOn != None)
                else: sql = sql.filter(BlogPostMapped.DeletedOn == None)
                
        else: sql = sql.filter((BlogPostMapped.PublishedOn == None) & (BlogPostMapped.DeletedOn == None))     
                                   
        sql = sql.order_by(desc_op(BlogPostMapped.Order))
        sqlLimit = buildLimits(sql, offset, limit)
 
        posts = self._addImages(self._trimPosts(sqlLimit.distinct(), deleted= not deleted, unpublished=False, published=True), thumbSize)
        if detailed:
            posts = IterPost(posts, sql.distinct().count(), offset, limit)
            
            lastCidSql = self.session().query(func.MAX(BlogPostMapped.CId))
            lastCidSql = lastCidSql.join(CollaboratorMapped, BlogPostMapped.Creator == CollaboratorMapped.User)
            lastCidSql = lastCidSql.filter(CollaboratorMapped.Source == sourceId)
            
            posts.lastCId = lastCidSql.scalar()
            
        return posts

def set_recent_outings(waypoint, lang):
    """Set last 10 outings on routes associated to the given waypoint.
    """
    t_outing_route = aliased(Association, name="a1")
    t_route_wp = aliased(Association, name="a2")
    with_query_waypoints = _get_select_children(waypoint)

    recent_outing_ids = get_first_column(
        DBSession.query(Outing.document_id)
        .filter(Outing.redirects_to.is_(None))
        .join(t_outing_route, Outing.document_id == t_outing_route.child_document_id)
        .join(
            t_route_wp,
            and_(
                t_route_wp.child_document_id == t_outing_route.parent_document_id,
                t_route_wp.child_document_type == ROUTE_TYPE,
            ),
        )
        .join(with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id)
        .order_by(Outing.date_end.desc())
        .limit(NUM_RECENT_OUTINGS)
        .all()
    )

    total = (
        DBSession.query(Outing.document_id)
        .filter(Outing.redirects_to.is_(None))
        .join(t_outing_route, Outing.document_id == t_outing_route.child_document_id)
        .join(
            t_route_wp,
            and_(
                t_route_wp.child_document_id == t_outing_route.parent_document_id,
                t_route_wp.child_document_type == ROUTE_TYPE,
            ),
        )
        .join(with_query_waypoints, with_query_waypoints.c.document_id == t_route_wp.parent_document_id)
        .count()
    )

    waypoint.associations["recent_outings"] = get_documents_for_ids(
        recent_outing_ids, lang, outing_documents_config, total
    )

        def filter_query(query):
            t_outing_route = aliased(Association, name='a1')
            t_route_wp = aliased(Association, name='a2')
            t_route = aliased(Document, name='r')

            return query. \
                join(
                    t_outing_route,
                    Outing.document_id == t_outing_route.child_document_id). \
                join(
                    t_route,
                    and_(
                        t_outing_route.parent_document_id ==
                        t_route.document_id,
                        t_route.type == ROUTE_TYPE)). \
                join(
                    t_route_wp,
                    and_(
                        t_route_wp.parent_document_id == waypoint_id,
                        t_route_wp.child_document_id == t_route.document_id))

def get_recent_friend_user_checkpoints(user_obj, limit = None):
    """
    (faux notification) returns Checkpoints that were recently created by friends
    """
    if limit == None:
        limit = 8
    
    from db import UserCheckpoint, db, FacebookUser, User, FriendConnection, Checkpoint
    
    FriendUserCheckpoint, FriendFacebookUser, FriendUser = aliased(UserCheckpoint), aliased(FacebookUser), aliased(User)
    q = (db.session.query(UserCheckpoint).
         join(Checkpoint, Checkpoint.id == UserCheckpoint.checkpoint_id).
         join(FriendUser, FriendUser.id == UserCheckpoint.user_id).
         join(FriendFacebookUser, FriendFacebookUser.id == FriendUser.facebook_user_id).
         join(FriendConnection, FriendConnection.fb_user_to == FriendFacebookUser.id).
         join(FacebookUser, FacebookUser.id == FriendConnection.fb_user_from).
         join(User, User.facebook_user_id == FacebookUser.id).
         filter(User.id == user_obj.id).
         filter(Checkpoint.creator == FriendUser.id).
         filter(Checkpoint.demo == False)
         )
    
    return q.order_by(desc(Checkpoint.date_created)).limit(limit).all()

def get_friends(user_obj, exclude_self=None):
    """
    returns a list of <<User>> objects, if exclude_self is True, it will not consider the current
    user as a friend.
    """
    if exclude_self == None:
        exclude_self = False
    
    from db import db, User, FriendConnection, FacebookUser
    
    FriendFacebookUser, FriendUser = aliased(FacebookUser), aliased(User)
    friends_q = (db.session.query(User, FriendUser).
               join(FacebookUser, FacebookUser.id == User.facebook_user_id).
               join(FriendConnection, FriendConnection.fb_user_from == FacebookUser.id).
               join(FriendFacebookUser, FriendConnection.fb_user_to == FriendFacebookUser.id).
               join(FriendUser, FriendUser.facebook_user_id == FriendFacebookUser.id)
               )
    friends_q = friends_q.filter(User.id == user_obj.id)
    
    friends = [f[1] for f in friends_q.all()]
    if not exclude_self:
        friends += [user_obj]
    
    return friends

def club(id):
    club = get_requested_record(Club, id)
    pilot_alias = aliased(User, name='pilot')

    columns = {
        0: (Flight, 'date_local'),
        1: (Flight, 'index_score'),
        2: (pilot_alias, 'name'),
        3: (Flight, 'olc_classic_distance'),
        4: (Airport, 'name'),
        5: (AircraftModel, 'name'),
        6: (Flight, 'takeoff_time'),
        7: (Flight, 'id'),
        8: (Flight, 'num_comments'),
    }

    return _create_list('club', request.args, club=club, columns=columns)

    def airport(self, id, **kw):
        airport = get_requested_record(Airport, id)
        pilot_alias = aliased(User, name='pilot')

        columns = {
            0: (Flight, 'date_local'),
            1: (Flight, 'index_score'),
            2: (pilot_alias, 'display_name'),
            3: (Flight, 'olc_classic_distance'),
            4: (Club, 'name'),
            5: (AircraftModel, 'name'),
            6: (Flight, 'takeoff_time'),
            7: (Flight, 'id'),
            8: (Flight, 'num_comments'),
        }

        return self.__do_list('airport', kw, airport=airport, columns=columns)

    def getUnpublished(self, blogId, typeId=None, creatorId=None, authorId=None, thumbSize=None, offset=None, limit=None,
                       detailed=False, q=None):
        '''
        @see: IBlogPostService.getUnpublished
        '''
        assert q is None or isinstance(q, QBlogPostUnpublished), 'Invalid query %s' % q
        
        postVerification = aliased(PostVerificationMapped, name='post_verification_filter')
        
        sql = self._filterQuery(blogId, typeId, creatorId, authorId, q)

        deleted = False
        if q:
            if QBlogPostUnpublished.isDeleted in q:
                deleted = q.isDeleted.value  
                
            if QWithCId.cId in q and q.cId:
                sql = sql.filter(BlogPostMapped.CId != None)
            sql = buildQuery(sql, q, BlogPostMapped)
            
            if QWithCId.status in q or QWithCId.checker in q:
                sql = sql.join(postVerification, postVerification.Id == BlogPostMapped.Id)     
                sql = sql.join(VerificationStatusMapped, VerificationStatusMapped.id == postVerification.statusId) 
                
            if QWithCId.status in q: 
                sql = sql.filter(VerificationStatusMapped.Key == q.status.equal) 
                
            if QWithCId.checker in q: 
                sql = sql.filter(postVerification.Checker == q.checker.equal)
                
            if QWithCId.cId not in q:
                sql = sql.filter(BlogPostMapped.PublishedOn == None) 
                if deleted: sql = sql.filter(BlogPostMapped.DeletedOn != None)
                else: sql = sql.filter(BlogPostMapped.DeletedOn == None)
                
        else: sql = sql.filter((BlogPostMapped.PublishedOn == None) & (BlogPostMapped.DeletedOn == None))    

        sql = sql.order_by(desc_op(BlogPostMapped.Order))
        
        sqlLimit = buildLimits(sql, offset, limit)
        posts = self._addImages(self._trimPosts(sqlLimit.distinct(), unpublished=False, published=True), thumbSize)
        if detailed:
            posts = IterPost(posts, sql.distinct().count(), offset, limit)
            posts.lastCId = self.session().query(func.MAX(BlogPostMapped.CId)).filter(BlogPostMapped.Blog == blogId).scalar()
        return posts

def pilot(id):
    pilot = get_requested_record(User, id)
    pilot_alias = aliased(User, name='pilot')

    mark_flight_notifications_read(pilot)

    columns = {
        0: (Flight, 'date_local'),
        1: (Flight, 'index_score'),
        2: (pilot_alias, 'name'),
        3: (Flight, 'olc_classic_distance'),
        4: (Airport, 'name'),
        5: (AircraftModel, 'name'),
        6: (Flight, 'takeoff_time'),
        7: (Flight, 'id'),
        8: (Flight, 'num_comments'),
    }

    return _create_list('pilot', request.args, pilot=pilot, columns=columns)