Complex SQL query into Zend

Question

I am trying to build a social network using Zend Framework. However, recently I have difficulty in making some complex SQL queries into Zend language. For example:

"SELECT t.plural_name, p.name as users_name, u.ID 
FROM users u, profile p, relationships r, relationship_types t 
WHERE t.ID=r.type 
      AND r.accepted=1 
      AND (r.usera={$user} OR r.userb={$user}) 
      AND IF( r.usera={$user},u.ID=r.userb,u.ID=r.usera) 
      AND p.user_id=u.ID"

How could I execute this query using Zend's select() object? Thank you very much!

score 0 · Answer 1 · edited Feb 11 '16 at 12:05

0

For really complex queries it might be easier just to use SQL. It is only important that all parameters going into the query are properly escaped to prevent SQL injections.

edited Feb 11 '16 at 12:05

marc_s

732,580
175
1,330
1,459

answered Jul 11 '12 at 09:28

Marko

514
1
4
16

score 0 · Answer 2 · answered Jul 11 '12 at 09:46

You can take this as example:(though its not complete)

<?php
$this->_query = $this->_DB->select()->from(array('u'=>'users'),array('(u.ID)'))
                                    ->join(array('p'=>'profile'),
                                               'p.user_id = u.ID',
                                               'p.name AS users_name')
                                     ->join(array('r'=>'relationships'),
                                               'r.userb = u.ID')    
                                    ->join(array('t'=>'relationship_types'),
                                               't.ID = r.type',
                                               't.plural_name')

                                    ->where('t.ID = r.type AND r.accepted = 1')
                                    ->where('r.usera = :user')
                                    ->orWhere('r.userb = :user')
                                    ->bind(array('user'=>$user));

score 0 · Accepted Answer · answered Jul 11 '12 at 12:09

Whenever I have complex queries to deal with, I usually try to run them in MySQL. Once I have found the exact SQL statement, I start to 'translate' it to Zend. I seems a bit complex but in the long run, you get to do them in Zend right away. Here is how I break it down. First figure out what the SQL statement is:

SELECT t.plural_name, p.name as users_name, u.ID 
FROM users u, profile p, relationships r, relationship_types t 
WHERE t.ID=r.type AND r.accepted=1 AND
 (r.usera={$user} OR r.userb={$user}) AND
 IF( r.usera={$user},u.ID=r.userb,u.ID=r.usera) AND
 p.user_id=u.ID

Now, you will want to convert all the relationships to joins. The joins are somewhat scary at the beginning but it's simply a way to put the table and the criteria on which it joins to another table in one line.

SELECT t.plural_name, p.name AS users_name, u.ID 
FROM users u
INNER JOIN profile AS p ON p.user_id = u.ID
INNER JOIN relationships AS r ON IF(r.usera={$user}, u.ID=r.userb, u.ID=r.usera)
INNER JOIN relationship_types AS t ON t.ID = r.type
WHERE
 (r.usera={$user} OR r.userb={$user}) AND
 r.accepted=1

Now that it is written in a more 'Zend friendly' way, you can easily start to convert it to Zend:

$select = $this->select()->setIntegrityCheck(false);
$select->from(array('u'=>'users'), '');
$select->join(array('p'=>'profile'), 'p.user_id = u.ID', '');
$select->join(array('r'=>'relationships'), 'IF(r.usera={$user}, u.ID=r.userb, u.ID=r.usera)', '');
$select->join(array('t'=>'relationship_types'), 't.ID = r.type', '');
$select->columns(array(
  't.plural_name', 
  'users_name'=>'p.name', 
  'u.ID'));
$select->where('r.usera={$user}');
$select->orWhere('r.userb={$user}');
$select->where('r.accepted=1');

And that should do the job.

$select->where('r.usera={$user}'); $select->join(array('r'=>'relationships'), 'IF(r.usera={$user}, u.ID=r.userb, u.ID=r.usera)', ''); Aren't these two overlapping? — Yangrui, Jul 12 '12 at 06:46

Complex SQL query into Zend

3 Answers3