PHQL DISTINCT Question.

Question

PHQL DISTINCT Question.

Andy Myers Jun '14

Created Jun '14	Last Reply Jun '14	Replies 2	Views 802	Votes 0

Andy Myers 10.9k

Jun '14


    $phql = "SELECT Orders.*, OrderItems.is_bespoke FROM Orders INNER JOIN OrderItems ON Orders.id = OrderItems.order_id WHERE Orders.status = 'order' AND OrderItems.is_bespoke = '1' ORDER BY Orders.id";

        $orders = $this->manager->executeQuery($phql);

        foreach ($orders as $key => $value) {
            debug($value->orders->name, false);
        }

I am going round in circles with this one. The above gives me the following result:

Lisa Pickard
Lisa Pickard
Michael Handford
Nazia Yousaf
Georgina Crawford
Joy Wymark
Stuart Rose
Stuart Rose
Julie Bennions
Mcdonald Mcdonald
Graham Kindley
Graham Kindley
Kelvin Davies
Sarah Norie
Emma Sharp

What I'm trying to acheive is to find all orders that have an order item that is marked as a bespoke item is_bespoke = 1 which the above orders are like that but I get them twice if it's more than one item and I would like to limit it to one if possible with some kind of DISTINCT version. Using SELECT DISTINCT Orders.id, Orders.name etc etc doesn't seem to work.

Max Galbusera
3.2k

Accepted
answer

Jun '14

try GROUP BY Orders.id before the ORDER BY clause

le51 · Answer 1 · 2014-06-18T15:10:05-07:00

le51
43.9k

Jun '14

hi, I had a similar problem

But it seems to be fixed now: look at this issue on github

Andy Myers · Answer 2 · 2014-06-19T01:34:18-07:00

Just as a point of interest, I managed to achieve the required result this morning by using a model filter. This is something I had not seen before.

    $orders = Orders::find('status="order"')->filter(function($order) {
            if($order->is_bespoke_order()) {
                return $order;
            }
        });

Where is_bespoke_order() is a method in my Order model that itterates through the order items and returns true should it find a bespoke item. The GROUP BY worked also but using 1.3.2 did not seem to make any change in regards to using DISTINCT. Hope this post helps somebody.