CodeIgniter Forums
JOIN problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: JOIN problem (/thread-26175.html)

Pages: 1 2


JOIN problem - El Forum - 01-07-2010

[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


JOIN problem - El Forum - 01-07-2010

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


JOIN problem - El Forum - 01-07-2010

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


JOIN problem - El Forum - 01-07-2010

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


JOIN problem - El Forum - 01-07-2010

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


JOIN problem - El Forum - 01-07-2010

[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


JOIN problem - El Forum - 01-07-2010

[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


JOIN problem - El Forum - 01-07-2010

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

        )

)



JOIN problem - El Forum - 01-08-2010

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


JOIN problem - El Forum - 01-09-2010

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