How to write a complex sql query in zend2

Question

I want to write a mySQL query like the following in zend framework 2.

How to convert it in zend Db select convention?

    SELECT profiles.*, 
    user.email,
    user.first_name,
    user.last_name,
    portfilio_images.profileImage,
    images.url AS imgurl
    FROM profiles

    INNER JOIN user ON user.user_id = profiles.ownerId

    LEFT JOIN (
        SELECT *
        FROM portfilio_images
        WHERE portfilio_images.profileImage = '1'
    ) as portfilio_images ON portfilio_images.profileId = profiles.id

    LEFT JOIN images ON images.id = portfilio_images.imageId

    WHERE profiles.ownerId != '4' AND (profiles.name LIKE '%a%' OR user.first_name LIKE '%a%' OR user.last_name LIKE '%a%')

    GROUP BY user.user_id

I have a write-up on Joins, perhaps it will help you: http://stackoverflow.com/questions/14354802/tablegateway-with-multiple-from-tables/14371056#14371056 — Diemuzi, Mar 07 '13 at 14:18
Thanks for you link Diemuzi, Actually in this query, I faced two difficulties. First one is it has an innser select query with condition and second one is placing where condition with parenthesis. It is complex to me as because I can not find to set condition with parenthesis so I did it for temporary as "$where[] = "(profiles.name LIKE '%" . $search . "%' OR user.first_name LIKE '%" . $search . "%' OR user.last_name LIKE '%" . $search . "%')";" but this is definitely I do not like. — Tahmina Khatoon, Mar 13 '13 at 05:31

score 1 · Answer 1 · answered Mar 07 '13 at 10:16

1

You can also use Zend\Db\Sql\Select that allows you to build complex queries programatically. See the documentation here:

http://zf2.readthedocs.org/en/release-2.1.1/modules/zend.db.sql.html#zend-db-sql-select

answered Mar 07 '13 at 10:16

GeeH

563
4
14

score 0 · Answer 2 · answered Mar 07 '13 at 09:13

You could always just perform a raw query:

$sql = "SELECT * FROM test";

$statement = $abAdapter->query($sql);
$result = $statement->execute();

$resultSet = new ResultSet;
$resultSet->initialize($result);

return $resultSet;

Or if you want to add some parameters

$sql = "SELECT * FROM test where col = ?";

$statement = $dbAdapter->query($sql);
$result = $statement->execute(array(99));

$resultSet = new ResultSet;
$resultSet->initialize($result);

return $resultSet;

How to write a complex sql query in zend2

2 Answers2