php - Mysql return 2 linked items, ordered by created and grouped by table id -

been trying think best way far have been stumped.

basically in 1 query want data 2 tables limit them in unorthodox way.

table1 parent of table2. need 2 table2 rows returned every 1 table1 row.

so in data looks this:

row 1   item 1   item 2 row 2   item 1   item 2  

but actual mysql data this:

row 1 item 1 row 1 item 2 row 2 item 1 row 2 item 2 

this easy enough if there no limitation on how items per table1 row there are, need limit them 2, order items created date, , group them table1 id.

i using laravel this, know situation db:raw used, here concept of got.

$data = db::table('table2')     ->select('table2.data', 'table2.created_at')     ->leftjoin('table1', 'table2.part_id', '=', 'table1.id')     ->orderby('table2.created_at')     ->groupby('table1.id'); 

i thinking raw join , try table subquery return 2 of every table1 item never had try create duplicate items deliberately before , feel totally wrong way go it.

anyone got thoughts on this?

oh , explain why not using model this. works using normal eloquent models, eloquent multiple queries , processes data on php side, specific query churns out alot of data, in order keep efficient want try in 1 query (and hitting php max execution time).

here sample of eventual output

item 1    previous (created at) - data   current (created_at) - data item 2   previous (created at) - data   current (created_at) - data 

so "sub items" logs, track "work" done item, need current , previous 1 reports, using test data have right 20,001 queries in 1 page load.

so solution attempted improper. not believe using queries way designed , biggest problem is. should consider tracking data differently being tracked.

i along lines of updated_at being recent update element. have history table record previous item according id. if needed save more data ok continue inputing data history, ever need pull recent id. understandably have large set of predefined data, if can change route consider.

problems not see solution are:

• the inability limit query in way 2 rows defined x while until x ends as talked can done in php seems work!
• inefficiency occur. apparently expected
• lost logic. (not 100% sure why started off way)

i wish more, can't think of solution within parameters of sql or efficient loop in php to keep structure have defined , correct results.

if using php loop not efficient, right. found solution works standards.

$data = db::table('table2')     ->select(db::raw('select table2.data,table2.created_at table2 table2.part_id=table1.id order table2.created_at desc limit 2'))     ... 

i don't have opportunity try this, can use raw query in execution , see if correct thing. should cover it, believe right regular query build not correctly.