3

I know Zend provides a having() method, but what I want is a query like:

SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a`
INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id
WHERE (a.id = 1) AND as.seed_name HAVING 'johnny'

not "HAVING (as.seed_name = 'johnny')"

Backtracking a bit, we have the tables:

fruit_db.apples

| id  | name |
--------------
|  1  | red  |
|  2  | green|

fruit_db.apple_seeds

| apple_id | seed_name | 
------------------------
| 1        | johnny    |
| 1        | judy      |
| 2        | granny    |

I want the results like:

| id  | name | apple_id | seed_name |
-------------------------------------
| 1   | red  |    1     | johnny    |
| 1   | red  |    1     | judy      |

The above query provided gives this result, but using Zend_Db_Select puts parenthesis around each portion of the having and where statements which invalidates my query. So

$zend_db_table->select()
 ->setIntegrityCheck(false)
 ->from(array("a" => "apples"), array("*"))
 ->join(array("as"=>"apple_seeds"),
     "a.id = as.apple_id",
   array("*"))
 ->where('a.id = 1')
 ->where('as.seed_name HAVING "johnny"');

produces:

SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a`
INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id
WHERE (a.id = 1) AND (as.seed_name HAVING 'johnny')

Which is invalid SQL. In short:

SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a`
INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id
WHERE (a.id = 1) AND as.seed_name HAVING 'johnny'

is valid, but:

SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a`
INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id
WHERE (a.id = 1) AND (as.seed_name HAVING 'johnny')

which Zend produces is invalid SQL. I don't want just the one row that has seen_name 'johnny', i want ALL rows where apple id = 1 AND seed_name 'johnny' is somewhere in those results. Can I get what I need via Zend_Db_Select or do I need to go the raw query() route?

Edit: I've revised the question a bit to be closer to what I want and trying to clarify it a bit.

thecheese
  • 31
  • 1
  • 3
  • I don't know anything about Zend, but I do know that HAVING is not part of a WHERE clause. So I'm pretty sure you're using the wrong method of building your query. HAVING is part of a GROUP BY... so maybe if you look at Zend's GROUP BY documentation, you'll find your answer. I don't know if HAVING is even valid without GROUP BY, but maybe it implies some magical GROUP BY in MySQL. I also don't think that 'HAVING 1' is a valid HAVING clause, either, but again... maybe magical MySQLness. – Jonathan Hall Oct 28 '11 at 06:44
  • I agree with Flimzy. You're query is wrong. You can use `->having()` method in Zend but still your query is invalid. – Peter Oct 28 '11 at 07:28
  • You're probably right Flimzy. I agree that I may be misusing HAVING in a way that might be implying some magical GROUP BY or something else, as I can assure you my first query using "as.seed_name HAVING 'johnny'" works as I want it to so long as there are no parenthesis around it, I just can't get Zend to produce it that way. Zend's ->having() unfortunately does not give the result set I'm looking for. I suppose i'll keep digging into the MySQL docs and just use a plain old ->query() for now. Thanks! – thecheese Oct 28 '11 at 18:05

1 Answers1

1

change

->where('as.apple_id HAVING 1');

to

->having('as.apple_id = 1');

http://framework.zend.com/manual/en/zend.db.select.html

nisav
  • 143
  • 1
  • 7
  • If we apply this to my revised question/query, it produces: SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a` INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id WHERE (a.id = 1) HAVING as.seed_name = 'johnny' The problem is that it returns only the one row where seed_name equals "johnny". I'm looking to get all rows where the apple_id is 1, but only if any of those rows have a seed_name of "johnny" – thecheese Oct 28 '11 at 17:44
  • then leave `->where('a.id = 1')` as is and instead change `->where('as.seed_name HAVING "johnny"')` to `->having('as.seed_name = "johnny"')` this should then product sql `where a.id = 1 having as.seed_name = 'johnny'` – nisav Nov 02 '11 at 15:27