phpmysqlzend-db-select

how to get this query into Zend_db_select

I have the following query:

SELECT *
FROM 
(SELECT products.uid, products.title, products.ean, products.description, products.price, products.date_publish, publishers.name, GROUP_CONCAT( CONCAT (authors.first_name, ' ', authors.last_name) SEPARATOR ', ') AS authors
FROM products
INNER JOIN publishers
ON products.uid_publisher = publishers.uid
INNER JOIN products_authors_mm
ON products.uid = products_authors_mm.uid_product
INNER JOIN authors
ON products_authors_mm.uid_author = authors.uid
GROUP BY products.uid) AS t
WHERE title LIKE '%jerzy%' OR name LIKE '%jerzy%' OR authors LIKE '%jerzy%'

When I try to put it into a zend_db_select query like that:

$selectProducts = $db->select()
        ->from('products', array('products.uid AS id',
            'products.title',
            'products.ean',
            'products.description',
            'products.price',
            'products.date_publish',
            'publishers.name',
            'GROUP_CONCAT( CONCAT (authors.first_name, \' \', authors.last_name) SEPARATOR \', \') AS authors'))
        ->join('publishers', 'products.uid_publisher = publishers.uid')
        ->join('products_authors_mm', 'products.uid = products_authors_mm.uid_product')
        ->join('authors', 'products_authors_mm.uid_author = authors.uid')
        ->group('products.uid');

$finalSelect = $db->select()
                ->from($selectProducts)
                ->where('t.title LIKE ?', '%' . $query . '%')
                ->orWhere('t.name LIKE ?', '%' . $query . '%')
                ->orWhere('t.authors LIKE ?', '%' . $query . '%');

MYSQL gives me the following error:

Message: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'name'

When I echo my $finalSelect, I get this query:

    SELECT `t`.* 
FROM 
(SELECT `products`.`uid` AS `id`, `products`.`title`, `products`.`ean`, `products`.`description`, `products`.`price`, `products`.`date_publish`, `publishers`.`name`, 
GROUP_CONCAT( CONCAT (authors.first_name, ' ', authors.last_name) SEPARATOR ', ') AS `authors`**, `publishers`.*, `products_authors_mm`.*, `authors`.*** 
FROM `products` 
INNER JOIN `publishers` ON products.uid_publisher = publishers.uid 
INNER JOIN `products_authors_mm` ON products.uid = products_authors_mm.uid_product 
INNER JOIN `authors` ON products_authors_mm.uid_author = authors.uid 
GROUP BY `products`.`uid`) AS `t` 
WHERE (t.title LIKE '%Andrzej%') OR (t.name LIKE '%Andrzej%') OR (t.authors LIKE '%Andrzej%')

When I remove publishers.*, products_authors_mm.*, authors.* from there, it works fine.

So my question is, how can I change my PHP code for this query to work?

Solution

  • When you do the joins, you need to tell it not to select the columns from the joined table.

    For example:

    ->join('publishers', 'products.uid_publisher = publishers.uid', **''**)