Cdbcriteria, Need Help To Split And Sort By Joined Subquery...

Hi, I’m Mikko and I work for Google/Bing/Facebook (basically Internet visibility) marketing company, mainly making code for controlling our database of sold campaigns and customer contacts for data management.

So I have these 2 tables I make list of, adw_customer and adw_order.

Basically adw_customer(Model: Customer) is a customer data, name and contacts and so forth, customers have their own ID called customer_id.

adw_order(Model: Order) is table for orders made by Customer, order’s have their own ID called order_id. Orders can have data like day created or when campaign is going online and so on.

I have dataprovider I pass into my index.php that creates CListView based on Customer:Model and criteria given on search.

DataProvider is currently doing fine job when we’re doing sorting by Customer. For example we can limit our search by campaign_name seller_id or similar.

I have this $criteria (Cdbcriteria) object I have given many conditions based on what we intend to search for. Basically simple search would only limit by: $criteria->condition = ‘customer_id in (551, 552, 553)’; or something similar, and that would basically mean that the on mysql_query there were 3 Customer that matched the results.

So the output will be something like:

Customer 551:

Order 67126 (seller_id = 14)

Order 21365 (seller_id = 72)

Order 15195 (seller_id = 55)

Customer 552:

Order 47126

Order 61365 (seller_id = 55)

Order 52165 (seller_id = 55)

Order 25195 (seller_id = 72)

Customer 553

Order 67626 (seller_id = 72)

Order 21875 (seller_id = 14)

To get Order data into CListView we join Order into result.

[b]$criteria->join = ‘LEFT JOIN adw_order ON adw_customer.customer_id = adw_order.customer_id’;

$criteria->with = array(

‘order’=>array(

‘together’=>false,

‘condition’=>$sub_condition,

‘order’=>$sub_order,

)

);[/b]

in here sub_order is the limitation where you have maybe limited only campaigns that are Google or some that are Bing based. Now these all work fine. The sorting also works fine when I only need to touch the elements inside Order.

What I’m having problem now is I’ve been given request to sort Orders under Customer by seller name, or simply seller_id in this case.

So I need to somehow split the Customer into multiple rows, so that there will be all orders under one seller first, then orders from other seller and so on. I need to have multiple Customer rows because there are(or can be) multiple seller_id’s present in single Customer.

Customer 551:

Order 21365 (seller_id = 72)

Customer 552:

Order 25195 (seller_id = 72)

Customer 553:

Order 67626 (seller_id = 72)

Customer 551:

Order 67126 (seller_id = 14)

Customer 553:

Order 21875 (seller_id = 14)

Customer 551:

Order 15195 (seller_id = 55)

Customer 552:

Order 61365 (seller_id = 55)

Order 52165 (seller_id = 55)

I’ve tried to solve this by doing this:

  • adding into select the following line:

    $criteria->select = ‘distinct(adw_order.seller_id) as seller_crop, adw_customer.*’;

  • adding seller_crop value in Model class.

    public $seller_crop;

  • disabling use of $criteria->with, so now I only have:

    $criteria->join = ‘LEFT JOIN adw_order ON adw_customer.customer_id = adw_order.customer_id’;

Now this way I have multiple rows of same Customer who has more than 1 seller in its campaigns. At the moment it’s showing all the Orders in every Customer. Theoretically there should be way to limit the Orders in one Customer row based on seller_crop value. Problem is I can’t remove the extra Orders from others sellers.

I had to do certain if-clauses inside CListView so that it’ll check if $seller_crop is set and then not paste the rows that are not in that same seller_id.

Now I’m in that part that it’ll correctly show the sellers in name order BUT I can’t get the conditions to work inside the Order. Even if I put inside $criteria->condition WHERE-clause more conditions it doesn’t work as it’s meant. If there is 1 row of Orders that match the criteria, then it still pastes all the other rows. For example if I set that campaign needs to be created 2012-01-01 or later, then even if only 1 campaign inside the Customer match, then it pastes all campaigns, even they have created time way before 2012.

And I really don’t think it’s recommended to add any sort of forced row removing inside CListView anyway so I need to completely remake the criteria so it will be the one who only takes inside the correct rows.

If I use $criteria->with then it doesn’t give double CUSTOMER rows that I will be needing and I don’t know how to solve this. If I limit for example created date then it will only show the campaigns that match the criteria. Now however they are all under same Customer.

Should I try use only $criteria->join or is there way to use $criteria->with that wouldn’t remove same double Customer AND use seller_crop to remove other sellers that do not match?

I don’t have problem with sorting though, $criteria->order = ‘(SELECT name FROM adw_users WHERE user_id = adw_order.seller_id) ASC’; this one works fine and makes the Customers appear so that they are in order made by seller name.

Things I’ve hit wall (when $criteria->with commented/disabled from use):

Tried putting condition to $criteria:

[b]$criteria->join = ‘LEFT JOIN adw_order ON adw_customer.customer_id = adw_order.customer_id’;

$criteria->condition .= ’ AND seller_crop = adw_order.seller_id’;[/b]

Column not found

Tried use ON-clause in $criteria->join:

$criteria->join = ‘LEFT JOIN adw_order ON adw_customer.customer_id = adw_order.customer_id AND seller_crop = adw_order.seller_id’;

Column not found

Sorry if I you have hard time reading, I try to clarify if you have problem understanding.

I found a shorter way to ask this, so if you had problem understanding or didn’t bother read it all:

What kind of mysql-query/mysql-result will be formed(or would be similar) when CDbCriteria is passed into CActiveDataProvider and how will I be able to change it so it will create duplicate rows.

For example, query:

"SELECT adw_customer., adw_order. FROM adw_customer LEFT JOIN adw_order USING (customer_id) WHERE adw_customer.company_name LIKE ‘%Car%’;

and result:

company_name, contact_email, contact_person, billing_address, billing_locality adw_order.seller_id

‘Car Sales AYX’, ‘ajase@afhjt.fi’, ‘Pasi Aad’, ‘Alikeravantie 30’, ‘Mäntsälä’, 150

‘Car Sales AYX’, ‘ajase@afhjt.fi’, ‘Pasi Aad’, ‘Alikeravantie 30’, ‘Mäntsälä’, 177

‘Js Used Cars’, ‘pastt@fhdrs.com’, ‘Asko Asrty’, ‘Mustikkamäentie 42’, ‘KERAVA’, 199

‘Sinicar Oy’, ‘hpasn@wqyh.com’, ‘Juha Yuifd’, ‘Kujanpääntie 30’, ‘Vantaa’, 199

‘Caravan Oy’, ‘afspo@asdg.fi’, ‘Atso Hadt’, ‘Mestarintie 2’, ‘Vojakkala’, 199