Welcome Guest, Not a member yet? Register   Sign In
JOIN problem
#1

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

[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).
#3

[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?
#4

[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.
#5

[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.
#6

[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 Sad
#7

[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
#8

[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
                )

        )

)
#9

[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.
#10

[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.




Theme © iAndrew 2016 - Forum software by © MyBB