复杂的代码点火器用户搜索查询 [英] Complex codeigniter user search query

问题描述

我有3个表的用户，个人资料和位置.这些表的结构为:

I have 3 tables user, profile and location. The structure of these tables are:

user表

user_id | email | pass | activated | banned

profile表

user_id | name | birthday | picture | gender | last_active

location表

user_id | city | country | latitude | longitude

现在是搜索条件:

1. 必须激活用户(1)才能显示在搜索结果中.
2. 不得禁止用户(0).
3. 根据纬度和经度，搜索结果必须位于附近.
4. 搜索结果必须按上次激活顺序排序.

要显示搜索，我需要用户名，图片，城市和国家/地区，年龄，性别和离线/在线状态.

To display a search I will need user name, picture, city and country, age, gender and offline/online status.

我有一个查询要按位置排序:

I have a query to order by location:

SELECT 
    latitude, longitude, 
    SQRT( POW( 69.1 * ( latitude - 52.58 ) , 2 ) + POW( 69.1 * ( - 1.12 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS distance
FROM 
    location 
ORDER BY 
    distance

任何人都可以为这些条件做出贡献来构建代码点火器查询吗?

Can anyone contribute to build a codeignitor query for these conditions?

推荐答案

您可以尝试围绕此查询进行构建

you could try build around this query

select l.latitude, l.longitude, 
SQRT( POW( 69.1 * ( l.latitude - 52.58 ) , 2 ) +
POW( 69.1 * ( - 1.12 - l.longitude ) * COS( l.latitude / 57.3 ) , 2 ) ) as distance
from user as u
left join profile as p
on p.user_id = u.id
left join location as l
on l.user_id = u.id
where u.activated=1 and u.banned=0
order by distance desc

我可以建议您在mysql中使用外键约束进行查找

May I suggest you lookup using foreign key constraints in mysql

这篇关于复杂的代码点火器用户搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！