Welcome Guest, Not a member yet? Register   Sign In
Stuck on a active record query
#1

[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! Smile Smile
#2

[eluser]Chad Fulton[/eluser]
I wish I had something to test this on, since I don't I suspect it may not be exactly right, but here's something like what you'll want:

Code:
$result = $this->db->select('discussions.*, entries.*, users.* count(entries.*) as entries', false)
                   ->join('entries', 'entries.discussion_id = discussions.id')
                   ->join('users', 'users.id = entries.user_id')
                   ->order_by('entries.created_date', 'desc')
                   ->group_by('entries.discussion_id')
                   ->get('discussions');
#3

[eluser]Chad Fulton[/eluser]
Oops, the above query definitely won't work. I'll leave the post up so I can explain the mistake. The problem is that you can't order by before a group by clause, and doing the order after a group by clause won't help you.

Here's what should be working code:

Code:
$result = $this->db->select('d.*, e.*, u.*, count(e.id) as entries')
                   ->join('(SELECT * FROM entries AS e ORDER BY e.created_date DESC) as e', 'e.discussion_id = d.id')
                   ->join('users as u', 'u.id = e.user_id')
                   ->group_by('d.id')
                   ->get('discussions as d');
#4

[eluser]Skinnpenal[/eluser]
Thank you so much for your reply, Chad.

I haven't quite gotten it to work yet, but it has definitely gotten me on the right track!

(Right now it gets all the data right, but it only lists one discussion)




Theme © iAndrew 2016 - Forum software by © MyBB