Using laminas DB to query a mysql database and perform a join it always selects all the columns from the joined table

Question

Using laminas DB to query a mysql database and perform a join it always selects all the columns from the joined table

naris opened this issue 3 years ago · 1 comments

Bug Report

Q	A
Version(s)	x.y.z

        "name": "laminas/laminas-db",
        "version": "2.12.0",

Summary

If you attempt to query 2 tables that have some columns with the same names it is not possible to get the data from the table in the from clause because even if you specify all the columns to query using columns() it always adds all the columns from the joined table to the query and then the hydrator uses the data form the joined table NOT the required data from the table in the from clause.

Current behavior

If I have table main with columns main_id, name, long_name, image
and table sub with columns main_id, sub_id, name, long_name, image

then attempt to query

    $select = $sql->select()
	->from(['s' => 'sub'])
            ->columns(['name', 'long_name', 'image'])
	->join(['m' => 'main'], 's.main_id= s.main_id');

it will generate
select s.name as name, s.long_name as long_name, s.image as image, m.* from sub as s inner join main as m on s.main_id = m.main_id

Then the hydrator will pull data from the columns that were automatically added (m.*) instead of the ones in column(). If you attempt to specify the table by using columns('s.name', 's.long_name', 's.image') it will fail because it automatically adds the alias of the the table in the from() so it will attempt to
select s.s.name as name, s.s.long_name as long_name, s.s.image as image, m.*

Even if the column names in all the tables are unique, the way it always automatically selects extra columns increases the amount of data that has to be queried, stored and transferred :(

How to reproduce

Attempting to execute any code that joins table should have this bug. However, it will probably not be noticed if the names in the tables are unique.

Expected behavior

It should query only the data specified in column() (if specified) and not automatically add the data from all the joined tables.

Answer 1 · 2021-04-09T04:45:57.000Z

This package is considered feature-complete, and is now in security-only maintenance mode, following a decision by the Technical Steering Committee.
If you have a security issue, please follow our security reporting guidelines.
If you wish to take on the role of maintainer, please nominate yourself

If you are looking for an actively maintained package alternative, we recommend:

doctrine/dbal