Post by largan » Tue Jan 22, 2013 7:35 am

Hello All,

I am trying to write a query that extracts the orders from the db including their id, options and model name.
However, I saw that all this information is spread into multiple tables... so I am stuck now :)

Any help will be very appreciated.

Thanks

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by MarketInSG » Tue Jan 22, 2013 9:01 am

use a join statement ::)

alternatively, use the models that are provided. That will save you from writing the statements again.


User avatar
Guru Member

Posts

Joined
Wed Nov 16, 2011 11:53 am
Location - Singapore

Post by largan » Thu Jan 31, 2013 2:45 pm

Thanks for the answer,

I will easy use the Join statement, but the problem that I am facing is that I don't know which are the tables that I have to join... the information for each order is spread in so many tables with unique ID in each table :)

What do you mean by "alternatively, use the models that are provided"? Where do I find these models?

Thanks again :)

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by MarketInSG » Thu Jan 31, 2013 3:22 pm

use the model files provided. $this->load->model('sale/order');

and call for the functions you need


User avatar
Guru Member

Posts

Joined
Wed Nov 16, 2011 11:53 am
Location - Singapore

Post by largan » Wed Feb 06, 2013 10:21 pm

Huh, I am not very good at that. I can do an sql query, but not sure how to use the $this->load->model('sale/order');
Any help will be very appreciated.

Thanks.

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by largan » Thu Feb 07, 2013 3:02 am

Ok, I am almost there...
SELECT *
FROM `order` RIGHT JOIN `order_option`
on order.order_id=order_option.order_id where order_status_id = 2 group by order_option.order_id

but for some reason I am not getting the records grouped by the order_id. Instead I am getting new row for each option

What I am missing? ???

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by Qphoria » Thu Feb 07, 2013 4:11 am

largan wrote:Ok, I am almost there...
SELECT *
FROM `order` RIGHT JOIN `order_option`
on order.order_id=order_option.order_id where order_status_id = 2 group by order_option.order_id

but for some reason I am not getting the records grouped by the order_id. Instead I am getting new row for each option

What I am missing? ???
try left join and group by order.order_id

Code: Select all

SELECT *
        FROM `order` o LEFT JOIN `order_option` oo
        ON (o.order_id = oo.order_id)
        WHERE o.order_status_id = 2
        GROUP BY o.order_id

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by largan » Thu Feb 07, 2013 5:26 am

Nope, that way shows only the first option row for each order_id

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by largan » Thu Feb 07, 2013 5:36 am

well, if it doesnt work that way... I will just write a nasty query :)

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by largan » Fri Feb 08, 2013 3:39 pm

I have managed to do the JOIN query. Here is how it looks like

Code: Select all

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id,  o.name, o.value SEPARATOR ',') AS Options 
FROM `order_option` AS o 
LEFT JOIN `order` AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am

Post by RCSimmons10 » Tue Feb 12, 2013 1:31 am

Perhaps two queries and a loop (or two) would do the trick.

Newbie

Posts

Joined
Mon May 14, 2012 7:40 pm

Post by alina123 » Wed Feb 13, 2013 6:44 pm

I am not very good at that. I can do an sql query, but Any help will be very appreciated. :)

Newbie

Posts

Joined
Wed Feb 13, 2013 5:25 pm

Post by largan » Sun Feb 17, 2013 6:39 pm

I have just extended the query to get the product picture as well. No need of a loop I think. Here is it if it might help someone :)

Code: Select all

$sql = "SELECT order_id, GROUP_CONCAT( CONCAT( '<img width=100 src=http://youropencart/image/', image, '>', '', model, '-' ) , 

Options, '' ) AS Options1
 FROM (
SELECT o.order_id, order_product.model AS model, GROUP_CONCAT( oo.name, oo.value
SEPARATOR ',' ) AS Options, product.image
FROM `order` AS o
INNER JOIN order_option AS oo ON o.order_id = oo.order_id
 INNER JOIN order_product ON oo.order_product_id = order_product.order_product_id
INNER JOIN product ON product.product_id = order_product.product_id
where o.order_status_id = 2 
GROUP BY oo.order_product_id
) AS oop
GROUP BY order_id";
Then you just print $id and $Options1

Newbie

Posts

Joined
Mon Jan 07, 2013 6:16 am
Who is online

Users browsing this forum: No registered users and 11 guests