I have the following tables:
pages:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| page_id | int(11) | NO | PRI | NULL | auto_increment |
| type | varchar(20) | NO | | NULL | |
| parent_id | int(11) | NO | | NULL | |
| title | varchar(255) | NO | MUL | NULL | |
| text | longtext | NO | MUL | NULL | |
+------------+--------------+------+-----+---------+----------------+
custom:
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| page_id | int(10) unsigned | NO | PRI | NULL | |
| key | varchar(255) | NO | PRI | NULL | |
| value | longtext | NO | | NULL | |
+---------+------------------+------+-----+---------+-------+
Whit the following query I get all the entries from table pages where type = 'questions'
and get the values from table custom where key = 'votes'
.
The problem here is this. In table pages I want to count all the entries with type = 'comments' AND parent_id = 'page_id'
SELECT * FROM pages AS P
LEFT JOIN custom AS C
ON P.page_id = C.page_id AND
C.key = 'votes'
WHERE
P.type = 'questions'
ORDER BY P.date DESC, C.value DESC
The problem is I have to inset somewhere SELECT COUNT(*) AS posts_count FROM pages WHERE page_id = parent_id
and the value from posts_count must be available in the query, because I want to order the entries by the order of comments AND after that by the votes from table custom