CODEWITHSUNDEEP
phpmysqlsqlzend-framework2
James R
James R

Reputation: 276

zf2 "join as" syntax

I have researched, but cannot find an answer to this problem. I am joining multiple tables which all use an "id".

The problem is i need to distinguish which id belongs to which table. In mysql it is ofcouse the AS command as such:

tableA.id AS tableAID

How can I incorporate this into a zf2 large query where several tables are using an id.

 $select = $sql->select();
 $select->from('tableName')
->columns(array('*'))
->join('tableA', 'tableA.id = tableB.id', array('id')) 
->join('tableC', 'tableC.id = tableB.id', array('id')) 
->where() //sql command continues

What I believe i need is something like this:

->join('tableA', 'tableA.id = tableB.id', array('id as tableAID')) 
->join('tableC', 'tableC.id = tableB.id', array('id as tableCID'))

I thought something like this may work but it breaks the code:

->join('tableA', 'tableA.id = tableB.id', array('id => tableAID'))

Thanks!

Upvotes: 0

Views: 161

Answers (3)

Mubo
Mubo

Reputation: 1070

if I understood what you need to realize. You can do the following. Joining Table A with Table B on the

 $select = $sql->select();
 $select->from(array('A',=>'tableA'));

 $select->join(array('B'=>'tableB'), 
              'A.id = B.A_id',array()
            );

Upvotes: 0

Nono HERON
Nono HERON

Reputation: 114

The best way to do for a simple select using joins is in my opinion not a zend way :

Just create a view in Sql !

CREATE VIEW V_My_Select AS
SELECT A.*, B.a_field, C.another_field
FROM table_a AS A 
INNER JOIN table_b AS B
    ON A.id = B.table_a_id
INNER JOIN table_c AS C
    ON A.id = C.table_a_id;

Then you only have to modify your entity to add the new properties added from your view (but I suppose you already did it to use your select using joins).

Then only make your select as simplys as you would do with any entity :

$this->$tableGateway->select();

Infact, the goal here is not only to simplify the zend coding of your select : => It's better for global performances to share the work between php and sql... the more treatments sql can manage (by views, triggers or stored procedures) the less php as to do...

Upvotes: 0

nick
nick

Reputation: 92

You were pretty close. To define an alias for a column just write:

->join('tableA', 'tableA.id = tableB.id', array('alias' => 'column_name'))

You just missed to end and begin a new string.

Upvotes: 1

Related Questions