Reducing number of columns in createQuery method e.g. using PARTIALs have no effect on final query

Inside my form I can add multiple Contacts:

    // Fields to be shown on create/edit forms
    protected function configureFormFields(FormMapper $formMapper)
    {
        $formMapper
                ->add('integratorContacts', 'sonata_type_model', array(
//                    'property' => 'user.username',
                    'property' => 'id',
                    'multiple' => true
                ))
        ;
    }
}

These Contacts have a subentity for User:

/**
 * @ORM\Entity
 * @ORM\Table(name="contacts")
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="contacttype", type="string")
 * @ORM\DiscriminatorMap({
 *      "integrator" = "IntegratorContact",
 *      "supplier"   = "SupplierContact"
 * })
 */
class ContactAbstract
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * ORM\ManyToOne(targetEntity="MyBundle\Entity\Contract", inversedBy="integratorContacts")
     * ORM\JoinColumn(name="contract_id", referencedColumnName="id")
     */
//    private $contract;

    /**
     * @ORM\OneToOne(targetEntity="Application\Sonata\UserBundle\Entity\User")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    private $user;

This will create a select with over 400 items from my User table. But that is the corrected behaviour and not the problem.

So far the query is fine too:

SELECT 
  t0.id AS id1, 
  t0.created_at AS created_at2, 
  t0.updated_at AS updated_at3, 
  t0.user_id AS user_id4, 
  t0.contract_id AS contract_id5, 
  t0.contacttype 
FROM 
  contacts t0 
WHERE 
  t0.contacttype IN ('integrator')

When I switch the property to the username on the User:

    // Fields to be shown on create/edit forms
    protected function configureFormFields(FormMapper $formMapper)
    {
        $formMapper
                ->add('integratorContacts', 'sonata_type_model', array(
                    'property' => 'user.username',
//                    'property' => 'id',
                    'multiple' => true
                ))
        ;
    }
}

it results on over 400 queries - each one of them a JOIN on the original User subentity to get the entity name.

Why is this neccessary? Shouldn't the JOIN be simply added to the original query (see above)?

Do I have to manage this using a custom queryBuilder?

So far I have found a solution for a different example reducing queries inside the list view using the createQuery method:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        $query->addSelect(
//                $query->getRootAlias() . '.id',
//                $query->getRootAlias() . '.name',
                'c', 'a'
            )
            ->leftJoin($query->getRootAlias() . '.customer', 'c')
            ->leftJoin('c.address', 'a')
        ;
//        echo $query->getDql();exit;
        return $query;
    }

This will reduce at least two join queries on each listed object.

Though I have not yet found a solution to select single columns only since the $query requires the complete object alias. Maybe PARTIALS?

Please see this issue for such a use case: https://github.com/sonata-project/SonataAdminBundle/issues/2300

Indeed PARTIALs seem to be a solution like in another example:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        $query->addSelect(
                'PARTIAL ' . $query->getRootAlias() . '.{id, createdAt}',
                'PARTIAL t.{id, name}',
                'PARTIAL p.{id}',
                'PARTIAL pu.{id, username}',
                'c',
                'a'
            )
            ->join($query->getRootAlias() . '.type', 't')
            ->join($query->getRootAlias() . '.partner', 'p')
            ->join($query->getRootAlias() . '.user', 'pu')
            ->leftJoin($query->getRootAlias() . '.customer', 'c')
            ->leftJoin('c.address', 'a')
            ->where($query->getRootAlias() . '.deletedAt IS NULL')
        ;

        return $query;
    }

Finally this will remove all extra queries for the JOINs.

In the end you get '''3''' queries:

SELECT COUNT(DISTINCT...) - Select the identifier only to count the rows including the JOINs
SELECT DISTINCT... - Select only the columns inside the PARTIAL on the $query->getRootAlias() including the JOINs
SELECT - Select EVERY column on the $query->getRootAlias() including EVERY column on the JOINs

Questions:

What is the second query used for?
Why does the third query select EVERY column? There seems to be some kind of reset but why not adapt the custom $query the user created?

I think the "reset" I managed is simply the rootAlias that is replacing the addSelect() when running the additional queries.

Actually I see no proper way for the queryBuilder to check if the user has used a PARTIAL on the rootAlias - in the end we have some more columns but the performance impact shouldn't be that big.

Still would like to know what the second query (see above) is use for?

I took another look at the 3 generated queries:

SELECT COUNT(DISTINCT...) - Select the identifier only to count the rows including the JOINs
SELECT DISTINCT... - Select only the columns inside the PARTIAL on the $query->getRootAlias() including the JOINs
SELECT - Select EVERY column on the $query->getRootAlias() including EVERY column on the JOINs

The 1st one does the general COUNT (which would be better as a wrapped query as requested in https://github.com/sonata-project/SonataAdminBundle/issues/2334):

SELECT 
  count(DISTINCT e0_.id) AS sclr0 
FROM 
  electrical_equipment e0_ 
  INNER JOIN product_categories p1_ ON e0_.product_category_id = p1_.id 
  LEFT JOIN media__media m2_ ON e0_.image_id = m2_.id 
  INNER JOIN locations l3_ ON e0_.location_id = l3_.id 
  INNER JOIN branches b4_ ON l3_.branch_id = b4_.id 
  LEFT JOIN media__media m5_ ON e0_.invoice_id = m5_.id 
  LEFT JOIN electrical_equipment_tests e6_ ON e0_.id = e6_.electrical_equipment_id 
  LEFT JOIN locations l7_ ON e0_.location_id = l7_.id 
  LEFT JOIN branches b8_ ON l7_.branch_id = b8_.id 
  LEFT JOIN product_categories p9_ ON e0_.product_category_id = p9_.id

The 2nd one only selects the IDs that will be used for the IN clause of the 3rd query:

SELECT 
  DISTINCT e0_.id AS id0, 
  e0_.id AS id1 
FROM 
  electrical_equipment e0_ 
  INNER JOIN product_categories p1_ ON e0_.product_category_id = p1_.id 
  LEFT JOIN media__media m2_ ON e0_.image_id = m2_.id 
  INNER JOIN locations l3_ ON e0_.location_id = l3_.id 
  INNER JOIN branches b4_ ON l3_.branch_id = b4_.id 
  LEFT JOIN media__media m5_ ON e0_.invoice_id = m5_.id 
  LEFT JOIN electrical_equipment_tests e6_ ON e0_.id = e6_.electrical_equipment_id 
  LEFT JOIN locations l7_ ON e0_.location_id = l7_.id 
  LEFT JOIN branches b8_ ON l7_.branch_id = b8_.id 
  LEFT JOIN product_categories p9_ ON e0_.product_category_id = p9_.id 
ORDER BY 
  e0_.id ASC 
LIMIT 
  10 OFFSET 0

The 3rd query will the SELECT the rows defined by the IDs SELECTed in the 2nd:

SELECT 
  e0_.id AS id0, 
  e0_.name AS name1, 
  e0_.description AS description2, 
  e0_.serialNumber AS serialNumber3, 
  e0_.invoiceDate AS invoiceDate4, 
  e0_.approvalNumber AS approvalNumber5, 
  e0_.created_at AS created_at6, 
  e0_.updated_at AS updated_at7, 
  e0_.id AS id8, 
  e0_.name AS name9, 
  e0_.description AS description10, 
  e0_.serialNumber AS serialNumber11, 
  e0_.invoiceDate AS invoiceDate12, 
  e0_.approvalNumber AS approvalNumber13, 
  e0_.created_at AS created_at14, 
  p1_.id AS id15, 
  p1_.name AS name16, 
  m2_.name AS name17, 
  m2_.description AS description18, 
  m2_.enabled AS enabled19, 
  m2_.provider_name AS provider_name20, 
  m2_.provider_status AS provider_status21, 
  m2_.provider_reference AS provider_reference22, 
  m2_.provider_metadata AS provider_metadata23, 
  m2_.width AS width24, 
  m2_.height AS height25, 
  m2_.length AS length26, 
  m2_.content_type AS content_type27, 
  m2_.content_size AS content_size28, 
  m2_.copyright AS copyright29, 
  m2_.author_name AS author_name30, 
  m2_.context AS context31, 
  m2_.cdn_is_flushable AS cdn_is_flushable32, 
  m2_.cdn_flush_at AS cdn_flush_at33, 
  m2_.cdn_status AS cdn_status34, 
  m2_.updated_at AS updated_at35, 
  m2_.created_at AS created_at36, 
  m2_.id AS id37, 
  l3_.id AS id38, 
  l3_.name AS name39, 
  b4_.id AS id40, 
  b4_.name AS name41, 
  m5_.name AS name42, 
  m5_.id AS id43, 
  e6_.id AS id44, 
  e6_.tested_at AS tested_at45, 
  e6_.comment AS comment46, 
  e0_.product_category_id AS product_category_id47, 
  e0_.image_id AS image_id48, 
  e0_.invoice_id AS invoice_id49, 
  e0_.location_id AS location_id50, 
  l3_.branch_id AS branch_id51, 
  l3_.compartment_id AS compartment_id52, 
  b4_.company_id AS company_id53, 
  b4_.compartment_id AS compartment_id54, 
  e6_.electrical_equipment_id AS electrical_equipment_id55, 
  e6_.user_id AS user_id56, 
  e6_.invoice_id AS invoice_id57 
FROM 
  electrical_equipment e0_ 
  INNER JOIN product_categories p1_ ON e0_.product_category_id = p1_.id 
  LEFT JOIN media__media m2_ ON e0_.image_id = m2_.id 
  INNER JOIN locations l3_ ON e0_.location_id = l3_.id 
  INNER JOIN branches b4_ ON l3_.branch_id = b4_.id 
  LEFT JOIN media__media m5_ ON e0_.invoice_id = m5_.id 
  LEFT JOIN electrical_equipment_tests e6_ ON e0_.id = e6_.electrical_equipment_id 
  LEFT JOIN locations l7_ ON e0_.location_id = l7_.id 
  LEFT JOIN branches b8_ ON l7_.branch_id = b8_.id 
  LEFT JOIN product_categories p9_ ON e0_.product_category_id = p9_.id 
WHERE 
  e0_.id IN (?) 
ORDER BY 
  e0_.id ASC, 
  e6_.tested_at DESC

But as you can see the 3rd query SELECTs every column though I defined my query with PARTIALs:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        $query->addSelect(
                'PARTIAL ' . $query->getRootAlias() . '.{id, name, description, serialNumber, approvalNumber, ' .
                'invoiceDate, createdAt}',
                'PARTIAL pc.{id, name}',
                'i1',
                'PARTIAL i2.{id, name}',
                'PARTIAL l.{id, name}',
                'PARTIAL lb.{id, name}',
                'PARTIAL t.{id, comment, testedAt}'
            )
            ->join($query->getRootAlias() . '.productCategory', 'pc')
            ->leftJoin($query->getRootAlias() . '.image', 'i1')
            ->join($query->getRootAlias() . '.location', 'l')
            ->join('l.branch', 'lb')
            ->leftJoin($query->getRootAlias() . '.invoice', 'i2')
            ->leftJoin($query->getRootAlias() . '.tests', 't')
        ;

        return $query;
    }

So while PARTIALs make sense when reducing number of columns for instance using the doctrine_orm_callback filter as mentioned in https://github.com/sonata-project/SonataAdminBundle/issues/2300#issuecomment-52252818 by @pulzarraider the don't seem to work as expected on the createQuery method inside the Admin Class.

Is there any way to make the 3rd query only select the defined columns?

This issue is very old. Can you please check this against the latest master version?

Feel free to reopen if this error still exists.

sonata-project / SonataAdminBundle

Reducing number of columns in createQuery method e.g. using PARTIALs have no effect on final query #2316