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

$builder->having() not working

I am writing a store locator, but I am having trouble with the HAVING clause. I am spitting out the query, and I keep getting a query without the HAVING clause. Am I missing something?

Here is my code:

                 $query = $this->modelsManager->createBuilder()
                        ->from('Stores')
                        ->columns(array('name',
                            'address',
                            '(
                                3959 * acos(
                                    cos(
                                        radians('.$area->latitude.')
                                    ) * cos(
                                        radians( Stores.latitude )
                                    ) * cos(
                                        radians( Stores.longitude ) - radians('.$area->longitude.')
                                    ) + sin(
                                        radians('.$area->latitude.')
                                    ) * sin(
                                        radians( latitude )
                                    )
                                )
                            ) AS distance'))
                        ->orderby('distance DESC')
                        ->having('distance < 1')
                        ->getQuery()->execute();
1


1.2k

Could you please paste the generated query (ie, what is passed to execute()) here?



1.2k

The error message you are getting would also be helpfull



5.1k

Here is the query that is generated:

SELECT stores.name AS name, stores.address AS address, (3959 acos(cos(radians(41.967255699999995)) cos(radians(stores.latitude)) cos(radians(stores.longitude) - radians(-87.6577926)) + sin(radians(41.967255699999995)) sin(radians(stores.latitude)))) AS distance FROM stores ORDER BY distance DESC

There is no error thrown by any of this. The problem is there is no HAVING clause being attached to the query.



1.2k

Unless I am missing something very obvious, your example in the OP does not call having() method.



5.1k

You are right; I'm sorry about that. I accidentally copied my changes for when I have ->where() instead ->having(). I just checked my code, and re-verified that when ->having() is called, nothing happens.



5.1k

I put the correct code in the OP, as well.



1.2k

Erm, but is it legal to have HAVING clause without a GROUP BY clause?



5.1k

I believe it is, in MySQL; however, I think Phalcon requires a ->groupBy() to add ->having().

Thank you for throwing that out there. That fixed my problem, however. Thank you very much. I was looking at the docs, and didn't see any requirements for it in them. All well!

Thanks again!



20

yes, it's legal and there are some cases when it's useful

Here is s bit weird (but still reasonable!) example: select count() from someTable having count() > 100 // Get amount of items if there are more than 100, else return nothing

I think when it used without "group by" it should works like regular "where", but this is only my guess.



1.2k

Dmitry, this is a MySQL extension and it is not protable to other DBMSes.

See, for example, this syntax diagram: https://www.sqlite.org/syntaxdiagrams.html#select-core



20

I think not, using having without group by is normal in Postgresql which is SQL99-compilant. https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING

I think using sqlite.org is not good place where we should look for reference implementation, just look how many useful features they don't implement in sqlite https://www.sqlite.org/omitted.html