zend-blog-3-backend
31 строка · 1.0 Кб
1CREATE VIEW `v_comments` AS
2SELECT
3c.id,
4c.parent_id,
5c.post_id,
6IF (c.user_id IS NULL, c.commentator_id, 10000000 + c.user_id) AS uid,
7IF (c.user_id IS NULL, t.name, u.username) AS username,
8IF (c.user_id IS NULL, t.mail, u.mail) AS mail,
9t.website,
10c.text,
11c.ip_addr,
12gci.city,
13gci.region,
14gci.latitude,
15gci.longitude,
16gci.time_zone,
17gco.country_name,
18gco.country_code,
19IF (c.user_id IS NULL, t.gender, u.gender) AS gender,
20c.deleted,
21COALESCE(ta.user_agent, 'unknown') AS user_agent,
22COALESCE(ta.is_bot, 0) AS is_bot,
23COALESCE(u.avatar_variant, 0) AS avatar_variant,
24c.time_created
25FROM comments AS c
26LEFT JOIN geo_location AS gl ON c.ip_long = gl.ip_long
27LEFT JOIN geo_location_city AS gci ON gl.city_id = gci.id
28LEFT JOIN geo_location_country AS gco ON gci.country_id = gco.id
29LEFT JOIN commentators AS t ON c.commentator_id = t.id
30LEFT JOIN users AS u ON c.user_id = u.id
31LEFT JOIN tracking_agent ta on c.user_agent_id = ta.id
32