wall like facebook - with a lot events AND comments for each event join problem ?!

[eluser]Richard Schumann[/eluser]
ok lets say you have something like a facebook wall with a lot events. now you wanna add a feature that you can comment a comment and OUTPUT all these events AND comments for each event in a common for-each loop. so that must be a join thing, but everything i tryed just gave me DOUBLE events - with several comments in the fields... OR if i group_by (event.id) just ONE comment - the LAST one - to be more detailed :-)


MYSQL

Code:

table EVENTS : id, value
table COMMENTS: id, event_id, value

so again : i want something like fallowing view:

EVENT1 blabla
COMMENT1 FOR EVENT1 blabla
COMMENT2 FOR EVENT1 blabla

EVENT 2 blabla
COMMENT1 FOR EVENT2 blablabla

EVENT 3 bla bla
COMMENT1 FOR EVENT3 blabla
COMMENT2 FOR EVENT3 blabla
COMMENT3 FOR EVENT3 blabla
COMMENT4 FOR EVENT3 blabla


so my model looks like this right now ( well more complex with more joins, AS, order_by, limit and so on,
but i think - thats the important part for this thread and this problem ?)

Code:

$result = $this
->db
->select('events.id, events.value, comments.id, comments.event_id, comments.value')
->from('events')
->join('comments', 'events.id = comments.event_id', 'left')
->get()
->result_array();

so how to get a result_array what is multi-dimensional maybe and contains then maybe a field COMMENTS what have id, value pairs in it ?


thank you so much again guys.

[eluser]Richard Schumann[/eluser]
push :-)

[eluser]Richard Schumann[/eluser]
ok a friend told me, that this is not posible because mysql is just2 dimensional and not 3dimensional . so i have to query for all events the comments in a loop after the query . with some tricks i can make it a bit more fast - like adding a field comment_counter in EVENTS and just query comments on comment_counter > 0 or something ....

you all agree that i cant do it with just one join in some way ?!

[eluser]Richard Schumann[/eluser]
seems i am the only guy in universe with that problem . a last push....

[eluser]jedd[/eluser]
Hi Richard,

It *sounds* like a common problem, but I think you might be confusing yourself more than the problem warrants.

Your schema that you describe seems sane, and so I'd point you at [url="http://ellislab.com/forums/viewthread/125879/#622401"]this reply in particular[/url] but read the whole thread for context.

Is your problem similar to this?

I think your friend's observation about dimensions are perhaps not entirely apt here, as you're only trying to join two tables in a fairly non-complicated way.

EDIT: I'd suggest you work on just getting the data coming out of MySQL in the format you want using raw SQL queries - at the mysql> prompt ideally - and then turn them back into CI's ActiveRecord format.