We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

PHQL DISTINCT Question.


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



3.2k
Accepted
answer

try GROUP BY Orders.id before the ORDER BY clause



43.9k

hi, I had a similar problem

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



10.9k

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.