[eluser]underzen[/eluser]
My brain is fried and I'm having a problem. I have 2 tables: orders and orders_items. The orders table contains the following fields: id, order_date. The orders_items table contains the following fields: id, order_id, product_id. What I want to do is for every id in the orders table join it with order_id in the orders_items table.
The query looks like this:
Code: SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`orders`)
JOIN `orders_items` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'
The code looks like this:
Code: $query = $this->db->select('orders.id, orders.order_date, orders_items.order_id, orders_items.product_id')->from('orders')->join('orders_items','orders_items.order_id = orders.id' ,'')->where('orders.customer_id' , 19)->get();
The output that I get is this:
Code: Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
)
[1] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 3
)
[2] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
The result I am trying to get is for 2 orders to be returned with the multiple associated product_id's from the orders_details table. So for order id 84 i want return the two product_id's from the orders_details table of 1 & 3. See below for an idea of the result I'm looking for.
Code: Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
[product_id] => 3
)
[1] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
My brain is so fried I'm not sure if this post will make sense to you all.
Thanks so much!!!!!
Eric
[eluser]danmontgomery[/eluser]
There isn't a way to do what you're describing, since that would require a result set with two identically named columns. What you probably want is GROUP_CONCAT, which will return all of the product_id's as a string in one field, you can specify a separator and explode them out that way. You'll also need to GROUP the results to get what you want.
SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, GROUP_CONCAT( DISTINCT `orders_items`.`product_id` SEPARATOR '|' ) AS product_ids
FROM (`orders`)
JOIN `orders_items` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'
GROUP BY `orders`.`id`
Should work for you, returning the multiple product id's as the string "1|3". (The 'DISTINCT' is probably optional, it will only give you distinct values in the case that there are 2 records for an order with the same product id).
[eluser]wowdezign[/eluser]
Did you try:
Code: $query = $this->db->select('orders.id, orders.order_date, orders_items.order_id, orders_items.product_id')->from('orders')->join('orders_items','orders_items.order_id = orders.id' ,'left')->where('orders.customer_id' , 19)->get();
and do a print_r() to see if you can work with that?
[eluser]wowdezign[/eluser]
Or if you want all the items, query that table and JOIN the orders to it. That might be what you are after. I might not have understood you correctly though.
[eluser]wowdezign[/eluser]
After re-reading your post:
Code: SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`order_items`)
JOIN `orders` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'
That should give you a result set containing the items. It will have repeated order.ids but it gives you the order_items grouped like you said, if I understood you right.
[eluser]underzen[/eluser]
[quote author="wowdezign" date="1262923649"]After re-reading your post:
Code: SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`order_items`)
JOIN `orders` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'
That should give you a result set containing the items. It will have repeated order.ids but it gives you the order_items grouped like you said, if I understood you right.[/quote]
First off, thank you so much for you help!
I tried the left join as you explained a few posts ago. That didn't work. I then tried changing the join around and I get the same exact result.
Code: SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`orders_items`)
JOIN `orders` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = 19
The result is the same:
Code: Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
)
[1] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 3
)
[2] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
I can't seem to figure this out 
[eluser]jmadsen[/eluser]
Hi underzen,
First of all, what you are trying to do from a DB perspective is virtually impossible, and I think if a solution could be found in pure sql (although it would be tremendous fun to work out :-) ) it would run like a three-legged pig.
Secondly, if you look at your desired result array, you see it doesn't make sense - you have the same array key repeated. You are simply going to run into problems trying to do it this way.
I am guessing that you are trying to create a "report" of some sort, and are trying to force your data into a nice format so you can just do a foreach{} on it. Don't! That's backwards.
Let your db produce a nice, fast "normal" result, and let your php parse your arrays (something it is good and fast at). Manipulate your data in the model or controller, not the query.
I think you just grabbed the bull by the wrong end and are making it unnecessarily difficult.
jeff
[eluser]Dyllon[/eluser]
Just foreach through the array to group your orders.
Code: $query_result = array(
array(
'id' => 84,
'order_date' => '2010-01-04 15:07:00',
'order_id' => 84,
'product_id' => 1
),
array(
'id' => 84,
'order_date' => '2010-01-04 15:07:00',
'order_id' => 84,
'product_id' => 3
),
array(
'id' => 85,
'order_date' => '2010-01-07 13:24:02',
'order_id' => 85,
'product_id' => 1
)
);
$orders = array();
foreach($query_result as $order)
{
$orders[$order['id']][] = $order;
}
print_r($orders);
gives this result
Code: Array
(
[84] => Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
)
[1] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 3
)
)
[85] => Array
(
[0] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
)
[eluser]wowdezign[/eluser]
Quote:I tried the left join as you explained a few posts ago. That didn’t work. I then tried changing the join around and I get the same exact result.
Did you switch the query order AND use the LEFT JOIN at the same time?
Because there is a difference between using JOIN and LEFT JOIN.
It would be easier for us to test our suggestions if we had the CREATE statement to generate the empty tables with.
[eluser]Sean Gates[/eluser]
@Dyllon has the right answer (@noctrum, you did great, too). Although I would modify this part of the code a little:
Code: $orders = array();
foreach($query_result as $order)
{
$orders[$order['id']][] = $order['product_id'];
}
print_r($orders);
You want to keep things short and sweet.
@wowdesign: From his first post it is clear that your join suggestions will not accomplish what he's looking for.
|
