Doctrine generating query with wrong table alias

Question

I'm trying to do a simple ->find() using doctrine/orm 2.5.1.

The query is quite simple:

$this->get('order_repository')->find(10);

But this is generating a complex query:

SELECT s0_.number AS number_0, s0_.state AS state_1, s0_.completed_at AS completed_at_2, s0_.items_total AS items_total_3, s0_.adjustments_total AS adjustments_total_4, s0_.total AS total_5, s0_.created_at AS created_at_6, s0_.updated_at AS updated_at_7, s0_.deleted_at AS deleted_at_8, s0_.id AS id_9, s0_.expires_at AS expires_at_10, s1_.currency AS currency_11, s1_.checkout_state AS checkout_state_12, s1_.payment_state AS payment_state_13, s1_.shipping_state AS shipping_state_14, s2_.quantity AS quantity_15, s2_.unit_price AS unit_price_16, s2_.adjustments_total AS adjustments_total_17, s2_.total AS total_18, s2_.is_immutable AS is_immutable_19, s2_.id AS id_20, s1_.channel_id AS channel_id_21, s1_.shipping_address_id AS shipping_address_id_22, s1_.billing_address_id AS billing_address_id_23, s1_.customer_id AS customer_id_24, s1_.offer_id AS offer_id_25, s2_.order_id AS order_id_26, s2_.variant_id AS variant_id_27 FROM sylius_order s1_ LEFT JOIN sylius_order_item s2_ ON s1_.id = s2_.order_id WHERE (s1_.id = 10) AND (s1_.deleted_at IS NULL)'

With the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 's0_.number' in 'field list'

I can see that s0_ is not defined anywhere on the query. Those alias are automatic from doctrine, i just don't see why it's using s0_ and then skipping it to s1_ on the FROM which has the columns mentioned on s0_.*

Answer 1

The problem is here: SELECT s0_.number as number_0 - it appears the column number is not defined or there is a difference between the mapping (annotations, yml, php) and schema (mysql).

Is there any SQL command if you run php app/console doctrine:schema:update --dump-sql? If it is, you have to update schema according to mapped entities.

Answer 2

I've just spent an hour on this same problem; You probably made the same typo mistake as me.

The problem for me was in the .yml schema definition. In the section where I defined the relations between my tables I had this target entity :

targetEntity: mlEmailNotif

instead of

targetEntity: MlEmailNotif

So the lowercase 'm' makes doctrine create a new alias for a new table (the php comparison is case sensitive).