zend-blog-3-backend
30 строк · 989.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, 1) AS gender,
20c.deleted,
21COALESCE(ta.user_agent, 'unknown') AS user_agent,
22COALESCE(ta.is_bot, 0) AS is_bot,
23c.time_created
24FROM comments AS c
25LEFT JOIN geo_location AS gl ON c.ip_long = gl.ip_long
26LEFT JOIN geo_location_city AS gci ON gl.city_id = gci.id
27LEFT JOIN geo_location_country AS gco ON gci.country_id = gco.id
28LEFT JOIN commentators AS t ON c.commentator_id = t.id
29LEFT JOIN users AS u ON c.user_id = u.id
30LEFT JOIN tracking_agent ta on c.user_agent_id = ta.id
31