Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Łukasz Czuliński
8,646 PointsTrying to order data from two tables in view.
I'm using Laravel but also looking for a raw MySQL solution too.
Basically what I want to do is pull data from two tables and have them ordered in the DOM sorted by a position column that is present in both tables (0, 1, 2, 3, 4, etc). Because of the position values, the results should often alternate upon rendering.
A foreach() won't work with two tables and a for() loop hasn't worked for me either. I can do it in Javascript but reordering the DOM via client-side feels dirty.
My latest failed attempt is something along these lines:
for($i = 0; $i < $positionCount; $i++){
if($notes[$i]->position === $i){
display the notes;
}
elseif($items[$i] === $i) {
echo $items;
}
}
This returns an undefined index which makes sense.
I've also tried the merge() function like:
$notes = Note::where('quote_id', '=', $quoteId)->orderBy('position', 'DESC')->get();
$items = Item::where('quote_id', '=', $quoteId)->orderBy('position', 'DESC')->get();
$all = $notes->merge($items);
But I still get undefined index when I try to iterate through $all.
4 Answers
Łukasz Czuliński
8,646 PointsEnded up getting to work. This is for different tables but same idea. I used a join:
$quotes = DB::table('quotes')->where('quotes.user_id', '=', Auth::id())
->join('clients', 'quotes.client_id', '=', 'clients.id')
->get();
Andreas cormack
Python Web Development Techdegree Graduate 33,011 PointsHi Łukasz Czuliński
if your simply trying to merge the arrays returned then what about just using the function array_merge but one thing to note that if the 2 arrays have duplicate keys the value in array 2 will get appended to the value in array 1.
php
$all=array();
$notes = Note::where('quote_id', '=', $quoteId)->orderBy('position', 'DESC')->get();
$items = Item::where('quote_id', '=', $quoteId)->orderBy('position', 'DESC')->get();
$all = array_merge($notes,$items);
'''
Łukasz Czuliński
8,646 PointsHi Andreas. Thanks for the answer. I tried array_merge() and it worked combining them. I then used usort() to sort by position column. Thanks! And the keys will never be duplicate.
Do you know of a way to combine them via the SQL queries though? I would prefer doing that if possible. I assume it would be some kind of join, since union wouldn't work as both tables are completely different except for a couple columns.
Andrew Shook
31,709 PointsŁukasz, so you want to print out both items and note's by index. For example:
<div id="item-zero">
<span> $item index zero text</span>
<span> $notes index zero text
</div>
<div id="item-one">
<span> $item index one text</span>
<span> $notes index one text
</div>
Is that what you are trying to do?
Łukasz Czuliński
8,646 PointsHi Andrew. Yes, that's what I'm after. I now got it to work as commented above. I'm looking to see if there might be a cleaner method of combining and sorting the results in a query first, rather than an array.
Andreas cormack
Python Web Development Techdegree Graduate 33,011 PointsŁukasz Czulińsk if your using laravel can you try the below code and see what you get?? must admit i haven't tried the hasMany method before so not sure.
first in your Note model
php
public function items(){
$this->hasMany('quote_id');
}
then in your controller
php
$all=Note->items()->where('quote_id', '=', $quoteId)->orderBy('position', 'DESC')->get();
``
Łukasz Czuliński
8,646 PointsSorry Andreas. I missed your last reply. I ended up getting it to work with a join. Will post answer below for future reference.