[eluser]Skinnpenal[/eluser]
Hi!
I'm completely stuck with a mysql query. Here's the case:
I've got 3 tables involved
* discussions
* entries
* users
And I want to
1: list up all discussions,
2: including the total number of entries in each
3: and the most recent query with its date
4: and author name.
So far I've only got #1 and #2 down:
Code:
$result = $this -> db
-> select('count(entries.id) as entries ', false)
-> select('discussions.*', false)
-> from('discussions', false)
-> join('entries', 'entries.discussion_id = discussions.id')
-> where('entries.discussion_id', 'discussions.id', false)
-> group_by('discussions.id')
-> get();
and now I somehow have to squeeze the result of these into the above:
Code:
SELECT user_id, created_date FROM entries WHERE discussion_id = discussions.id ORDER BY created_date DESC LIMIT 1
And
Code:
SELECT username FROM users WHERE id = entries.user_id LIMIT 1
Any ideas on how to solve this will be greatly appreciated!