[SF][Doctrine2] subquery i niepoprawne wypelnianie Encji Opcje

[marcio]

Mam pewien problem.
Posiadam w encje Gallery i GalleryImage:

Gallery:

[PHP] pobierz, plaintext 
/**
 * Meritoo\Cmf\GalleryBundle\Entity\Gallery
 *
 * @ORM\Table(name="cmf_gallery")
 * @ORM\Entity(repositoryClass="Meritoo\Cmf\GalleryBundle\Repository\GalleryRepository")
 */
class Gallery implements Translatable {
 
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
 
    /**
     * @var string $title
     *
     * @ORM\Column(name="title", type="string", length="100")
     * @Gedmo\Translatable
     */
    private $title;
 
    /**
     * @var string $title_slug
     *
     * @ORM\Column(name="title_slug", type="string", length="100")
     * @Gedmo\Slug(fields={"title"})
     * @Gedmo\Translatable
     */
    private $title_slug;
 
    /**
     * @var text $description
     *
     * @ORM\Column(name="description", type="text", nullable="true")
     * @Gedmo\Translatable
     */
    private $description;
 
    /**
     *
     * @var integer $position
     * 
     * @ORM\Column(name="position", type="smallint", nullable="true")
     */
    private $position;
 
    /**
     * @var datetime $created_at
     *
     * @ORM\Column(name="created_at", type="datetime")
     * @Gedmo\Timestampable(on="create")
     */
    private $created_at;
 
    /**
     * @var datetime $updated_at
     *
     * @ORM\Column(name="updated_at", type="datetime", nullable="true")
     * @Gedmo\Timestampable(on="update")
     */
    private $updated_at;
 
    /**
     * @var datetime $deleted_at
     *
     * @ORM\Column(name="deleted_at", type="datetime", nullable="true")
     */
    private $deleted_at;
 
    /**
     * @ORM\OneToMany(targetEntity="GalleryImage", mappedBy="gallery")
     */
    private $images;
 
    /**
     * The department, branch of the company / institution
     * 
     * @ORM\ManyToOne(targetEntity="Meritoo\Cmf\DepartmentBundle\Entity\Department", inversedBy="galleries")
     * @ORM\JoinColumn(name="department_id", referencedColumnName="id")
     * 
     * @var \Meritoo\Cmf\DepartmentBundle\Entity\Department
     */
    private $department;
 
    /**
     * The locale value (language code), e.g. pl_PL.
     * This is not a mapped field of entity metadata, just a simple property.
     * 
     * @var string
     * @Gedmo\Locale
     */
    private $locale;
[PHP] pobierz, plaintext 

GalleryImage:

[PHP] pobierz, plaintext 
/**
 * Meritoo\Cmf\GalleryBundle\Entity\GalleryImage
 *
 * @ORM\Table(name="cmf_gallery_images")
 * @ORM\Entity(repositoryClass="Meritoo\Cmf\GalleryBundle\Repository\GalleryImageRepository")
 * @ORM\HasLifecycleCallbacks
 */
class GalleryImage implements Translatable {
 
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
 
    /**
     * @var string $title
     *
     * @Gedmo\Translatable
     * @ORM\Column(name="title", type="string", length="50")
     */
    private $title;
 
    /**
     * @var string $file_name
     *
     * @ORM\Column(name="file_name", type="string", length="100")
     */
    private $file_name;
 
    /**
     *
     * @var integer $position
     * 
     * @ORM\Column(name="position", type="smallint", nullable="true")
     */
    private $position;
 
    /**
     * @var boolean $is_main
     * 
     * @ORM\Column(name="is_main", type="boolean")
     */
    private $is_main = false;
 
    /**
     * @var datetime $created_at
     *
     * @ORM\Column(name="created_at", type="datetime")
     * @Gedmo\Timestampable(on="create")
     */
    private $created_at;
 
    /**
     * @var datetime $updated_at
     *
     * @ORM\Column(name="updated_at", type="datetime", nullable="true")
     * @Gedmo\Timestampable(on="update")
     */
    private $updated_at;
 
    /**
     * @var datetime $deleted_at
     *
     * @ORM\Column(name="deleted_at", type="datetime", nullable="true")
     */
    private $deleted_at;
 
    /**
     * @ORM\ManyToOne(targetEntity="Gallery", inversedBy="images")
     * @ORM\JoinColumn(name="gallery_id", referencedColumnName="id")
     * 
     * @var \Meritoo\Cmf\GalleryBundle\Entity\Gallery
     */
    private $gallery;
 
    /**
     * @Assert\File(maxSize="6000000")
     * @var \Symfony\Component\HttpFoundation\File\UploadedFile
     */
    private $file;
 
    /**
     * Helper for uploading.
     * Used for upload operations etc.
     * 
     * @var \Meritoo\Cmf\CommonBundle\Helper\UploadHelper
     */
    private $uploadHelper;
 
    /**
     * The locale value (language code), e.g. pl_PL.
     * This is not a mapped field of entity metadata, just a simple property.
     * 
     * @var string
     * @Gedmo\Locale
     */
    private $locale;
 
[PHP] pobierz, plaintext 

Musze pobrac za pomoja 1 zapytania wszystkie galerie ktore posiadaja przynajmniej 1 zdjecie, czyli pomijamy wszystkie puste galerie.
Mam metode w repository:

[PHP] pobierz, plaintext 
    /**
     * Returns all galleries
     * 
     * @param [integer $limit = 0] Maximum amount of items
     * @param [integer $offset = 0] The start position, offset
     * @param [boolean $skipEmpty = false] if is set to true galleries without images are skipped. Otherwise not.
     * @return array
     */
    public function getGalleries($limit = 0, $offset = 0, $skipEmpty = false) {
        if (!$skipEmpty) {
            $orderBy = array(
                'position' => 'ASC',
                'created_at' => 'DESC'
            );
 
            $criteria = $this->getCriteriaParameters();
            $galleries = $this->getResult($criteria, $orderBy, $limit, $offset);
        } else {
            $skipEmptyGalleriesSubQuery = $this->createQueryBuilder('g')
                    ->select('g.id')
                    ->leftJoin('g.images', 'i', \Doctrine\ORM\Query\Expr\Join::WITH, 'i.deleted_at is null')
                    ->where('g.deleted_at is null')
                    ->andWhere('i.id is not null')
                    ->groupBy('g.id')
                    ->getDQL();
 
            $queryBuilder = $this->createQueryBuilder('c');
            $queryBuilder->where($queryBuilder->expr()->in('c.id', $skipEmptyGalleriesSubQuery))
                    ->orderBy('c.position', 'ASC')
                    ->addOrderBy('c.created_at', 'DESC');
 
            if ($limit > 0) {
                $queryBuilder->setMaxResults($limit);
            }
 
            if ($offset > 0) {
                $queryBuilder->setFirstResult($offset);
            }
 
            $galleries = $queryBuilder->getQuery()->getResult();
        }
        return $galleries;
    }
[PHP] pobierz, plaintext 

W czym problem.
Mianowicie, zapytanie subquery wyglada dobrze zwraca mi id galeri ktore maja przynajmniej 1 zdjecie.
Glowne zapytanie zwraca mi kolumny(obiekt Gallery) dla nie pustych galerii sprawdzalem w phpmyadmin.
Jednak w "kodzie" zwraca obiekty ale z pustymi wlasciwosciami.
Jedyna metoda z Encji ktora nie zwraca pustej wartosci to:

[PHP] pobierz, plaintext 
//przyklad opiera sie o foreach() dla wartosci ktora zwraca getGalleries()
print $entity->getId(); //ok
print $entity->getTitle(); //i tu juz jest pusto mamy pusty string
[PHP] pobierz, plaintext 

I tak samo jesli chodzi o wszystkie inne pola.

Czy ktos wie gdzie moze byc problem?

Tak wyglada wygenerowane zapytanie:

[SQL] pobierz, plaintext 
SELECT c0_.id AS id0, c0_.title AS title1, c0_.title_slug AS title_slug2, c0_.description AS description3, c0_.position AS position4, c0_.created_at AS created_at5, c0_.updated_at AS updated_at6, c0_.deleted_at AS deleted_at7, c0_.department_id AS department_id8
FROM cmf_gallery c0_
WHERE c0_.id
IN (
 
SELECT c1_.id
FROM cmf_gallery c1_
LEFT JOIN cmf_gallery_images c2_ ON c1_.id = c2_.gallery_id
AND (
c2_.deleted_at IS NULL
)
WHERE c1_.deleted_at IS NULL
AND c2_.id IS NOT NULL
GROUP BY c1_.id
)
ORDER BY c0_.position ASC , c0_.created_at DESC
LIMIT 3 
[SQL] pobierz, plaintext 

[BugsBunny]

Spróbuj z modelu usunąć dla pola title @Gedmo\Translatable. Według mnie w tym tkwi problem.

Jak nie pomoże to sprawdź jeszcze czy usuwając transable dla pola description też będzie puste. Być może Doctrine nie radzi sobie z zapytaniem i miesza obiekty czyli title wsadziło Ci z GalleryImage, ale to raczej mało prawdopodobne.

[marcio]

Ok sprawdze juz jutro rano.
Choc te adnotacje sa potrzebne.

Chce tylko przypomniec ze w ogole nie ustawil wartosci oprocz id, podam kawalek var_dump-a:

Kod

object(Meritoo\Cmf\GalleryBundle\Entity\Gallery)#1296 (11) { ["id":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> int(10) ["title":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> string(0) "" ["title_slug":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> string(0) "" ["description":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> string(0) "" ["position":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> NULL ["created_at":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> object(DateTime)#1230 (3) { ["date"]=> string(19) "2012-09-20 09:48:54" ["timezone_type"]=> int(3) ["timezone"]=> string(13) "Europe/Berlin" } ["updated_at":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> object(DateTime)#1300 (3) { ["date"]=> string(19) "2012-09-20 09:48:54" ["timezone_type"]=> int(3) ["timezone"]=> string(13) "Europe/Berlin" } ["deleted_at":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> NULL ["images":"Meritoo\Cmf\GalleryBundle\Entity\Gallery":private]=> object(Doctrine\ORM\PersistentCollection)#1289 (9) { ["snapshot":"Doctrine\ORM\PersistentCollection":private]=> array(0) { } ["owner":"Doctrine\ORM\PersistentCollection":private]=> *RECURSION* ["association":"Doctrine\ORM\PersistentCollection":private]=> array(15) { ["fieldName"]=> string(6) "images" ["mappedBy"]=> string(7) "gallery" ["targetEntity"]=> string(45) "Meritoo\Cmf\GalleryBundle\Entity\GalleryImage" ["cascade"]=> array(0) { }

Jak widac wszystko jest ok tylko title,title_slug i description nie.
Wiec zapewne chodzi o @Gedmo\Transatable, to musi byc.
Zobacze dokladnie inne repozytoria/helpery systemu zeby naprawic ten problem.

Ewentualnie jak to mozna poprawic utrzymujac przy tym @Gedmo\Translatable

Spróbuj z modelu usunąć dla pola title @Gedmo\Translatable. Według mnie w tym tkwi problem.

Jak nie pomoże to sprawdź jeszcze czy usuwając transable dla pola description też będzie puste. Być może Doctrine nie radzi sobie z zapytaniem i miesza obiekty czyli title wsadziło Ci z GalleryImage, ale to raczej mało prawdopodobne.

Rozwiazalem to metoda setHints(), twoja odp. naprowadzila mnie na rozwiazanie

Ten post edytował marcio 20.09.2012, 23:50:43