Perform an Inner Join in Zend PHP Framework

Question

I want to perform an inner join on two table.

Table A -

item_id

item_title

varX

Table B -

item_id

varY

someVar

This is how I've done this using a RAW SQL query.

$sql = 'SELECT tableA.item_id, tableY.item_title AS Name, 5 * varX +  5 * count(*) AS myScore 
FROM tableA 
INNER JOIN tableY ON tableA.item_id=tableY.item_id 
WHERE someVar=\'8\' 
GROUP BY item_id
ORDER BY myScore DESC
LIMIT 10';
$stmt = $this->_db->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

Now I want to do this using a Zend Query.

This is what I've written -

$data = $this->_db->select()
->from(array('tablA'=>'tableA'), array('item_id', 'item_title'), 'myScore'=>'(5*'tableA'.'varX') + 5*count(*)')
->joinInner(array('tablB'=>'tableB'), 'tablA'.'item_id' = 'tablB'.'item_id')
->where('someVar = 8')
->GROUP('item_id')
->order('myScore DESC')
->limit(10);

$dataResult = $this->_db->fetchAll($data);

But I get this error -

 syntax error, unexpected '=>' (T_DOUBLE_ARROW), expecting ',' or ')'

in line ->from(array('tablA'=>'tableA'), array('item_id', 'item_title'), 'myScore'=>'(5'tableA'.'varX') + 5*count()')

Not sure what to do do here as I've read the official documentation but still can't figure this out. Any help is appreciated!

Answer 1

The quotes were used in wrong way in your code. Also, tt seems like, you have used the unnecessary third parameter for 'myScore' field. It should be placed in the second parameter
Try the following:

...
$data = $this->_db->select()
->from(array('tablA'=>'tableA'), array('item_id', 'item_title', 'myScore'=>'(5 * tableA.varX) + 5*count(*)'))
->joinInner(array('tablB'=>'tableB'), 'tablA.item_id = tablB.item_id')
->where('someVar = 8')
->group('tablA.item_id')
->order('myScore DESC')
->limit(10);