小贝子编程

原则2:在单表继承中，在一对多关联中获取所有具有或不具有匹配记录的记录



我正试图根据挂牌位置查找每个类别中的挂牌总数

我想做的是，如果一个来自美国的用户访问该网站，则只显示该位置的记录。此外，侧边栏上的类别小部件应该考虑该位置每个类别下的列表总数。如果访问者点击Chicago，小部件应重新计算以反映位置"Chicago">

代码低于

public function findCategoriesWithTotalListing($location)
{
$builder = $this->_em->createQueryBuilder();
$builder->select('c, count(l.id) AS num')
->from('BundleAdvertBundleEntityCategory', 'c')
->leftJoin('c.listings','l')
->leftJoin('l.country','co')
->leftJoin('l.state','st')
->leftJoin('l.city','ci');
$builder->groupBy('c.id');
$result = $builder->getQuery()->getArrayResult();
print_r($result);
exit;
}

我可以在没有位置参数的情况下检索所有类别和每个列表的总数。

但是，如果我引入位置参数，则只检索那些在该位置有列表的类别。

public function findCategoriesWithTotalListing($location)
{
$builder = $this->_em->createQueryBuilder();
$builder->select('c, count(l.id) AS num')
->from('BundleAdvertBundleEntityCategory', 'c')
->leftJoin('c.listings','l')
->leftJoin('l.country','co')
->leftJoin('l.state','st')
->leftJoin('l.city','ci');
$orx = $builder->expr()->orX();
$orx->add($builder->expr()->like("co.slug", ':location'));
$orx->add($builder->expr()->like("st.slug", ':location'));
$orx->add($builder->expr()->like("ci.slug", ':location'));
$builder->andWhere($orx);
$builder->groupBy('c.id');
$builder->setParameter('location',$location);
$result = $builder->getQuery()->getArrayResult();
print_r($result);
exit;
}

我想要所有类别的

我可能做错了什么

感谢

以下的实体

类别实体

<?php
namespace BundleAdvertBundleEntity;
use BundleFrameworkBundleEntityBaseEntity;
use BundleFrameworkBundleEntityDocumentInterface;
use DoctrineORMMapping as ORM;
/**
* Category
*
* @ORMTable(name="listings_categories")
* @ORMEntity(repositoryClass="BundleAdvertBundleRepositoryCategoryRepository")
*/
class Category extends BaseEntity implements DocumentInterface
{
/**
* @var integer
*
* @ORMColumn(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
* @ORMId
* @ORMGeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var string
*
* @ORMColumn(name="title", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
private $title;
/**
* @var string
*
* @ORMColumn(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
private $slug;
/**
* @var string
*
* @ORMColumn(name="summary", type="text", precision=0, scale=0, nullable=false, unique=false)
*/
private $summary;
/**
* @var integer
*
* @ORMColumn(name="parent", type="integer", precision=0, scale=0, nullable=false, unique=false)
*/
private $parent;
/**
* @var DoctrineCommonCollectionsCollection
*
* @ORMOneToMany(targetEntity="ListingItem", mappedBy="category")
*/
private $listings;
/**
* @var string
*
* @ORMColumn(name="thumbnail", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
*/
private $thumbnail;
/**
* @var string
*
* @ORMColumn(name="form_template", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
*/
private $formTemplate;

private $children;

private $totalChildren;
private $totaListing;

/* Getters and Setters*/

}

上市实体

<?php
namespace BundleAdvertBundleEntity;
use DoctrineORMMapping as ORM;
use BundleFrameworkBundleEntityDocumentInterface;
use BundleFrameworkBundleEntityBaseEntity;

/**
* @ORMEntity (repositoryClass="BundleAdvertBundleRepositoryListingItemRepository")
* @ORMTable(name="listings")
* @ORMInheritanceType("SINGLE_TABLE")
* @ORMDiscriminatorColumn(name="listing_type", type="string")
* @ORMDiscriminatorMap({
"forsale" = "ForsaleItem", 
"service" = "ServiceItem",
"job" = "JobItem",
"realestate" = "RealEstateItem",
"land" = "LandItem",
"house" = "HouseItem"
})
*/
abstract class ListingItem extends BaseEntity
{
/**
* @var integer
*
* @ORMColumn(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
* @ORMId
* @ORMGeneratedValue(strategy="IDENTITY")
*/
protected $id;
/**
* @var string
*
* @ORMColumn(name="title", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $title;
/**
* @var string
*
* @ORMColumn(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $slug;

/**
* @var string
*
* @ORMColumn(name="price", type="decimal", precision=0, scale=2, nullable=false, unique=false)
*/
protected $price;

/**
* @var string
*
* @ORMColumn(name="description", type="text", precision=0, scale=0, nullable=false, unique=false)
*/
protected $description;
/**
* @var string
*
* @ORMColumn(name="thumbnail", type="string", length=255, precision=0, scale=0, nullable=true, unique=false)
*/
protected $thumbnail;
/**
* @var string
*
* @ORMColumn(name="status", type="string", length=100, precision=0, scale=0, nullable=false, unique=false)
*/
protected $status;
/**
* @var DateTime
*
* @ORMColumn(name="date_created", type="datetime", precision=0, scale=0, nullable=true, unique=false)
*/
protected $dateCreated;
/**
* @var DateTime
*
* @ORMColumn(name="date_approved", type="datetime", precision=0, scale=0, nullable=true, unique=false)
*/
protected $dateApproved;
/**
* @var BundleAdvertBundleEntityCategory
*
* @ORMManyToOne(targetEntity="BundleAdvertBundleEntityCategory", inversedBy="listings")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="category_id", referencedColumnName="id", nullable=true)
* })
*/
protected $category;
/**
* @var BundleUserBundleEntityUser
*
* @ORMManyToOne(targetEntity="BundleUserBundleEntityUser", inversedBy="listings")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="user_id", referencedColumnName="id", nullable=true)
* })
*/
protected $user;
/**
* @var string
*
* @ORMColumn(name="name", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $name;
/**
* @var string
*
* @ORMColumn(name="email", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $email;
/**
* @var string
*
* @ORMColumn(name="contact_number", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $contactNumber;
/**
* @var BundleLocationBundleEntityCountry
*
* @ORMManyToOne(targetEntity="BundleLocationBundleEntityCountry")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="country_id", referencedColumnName="id", nullable=true)
* })
*/
private $country;
/**
* @var BundleLocationBundleEntityState
*
* @ORMManyToOne(targetEntity="BundleLocationBundleEntityState")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="state_id", referencedColumnName="id", nullable=true)
* })
*/
private $state;
/**
* @var BundleLocationBundleEntityCity
*
* @ORMManyToOne(targetEntity="BundleLocationBundleEntityCity")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="city_id", referencedColumnName="id", nullable=true)
* })
*/
private $city;

protected $dateformat = 'd-M-Y';
}

位置实体

use DoctrineORMMapping as ORM;
/**
* @ORMEntity (repositoryClass="BundleLocationBundleRepositoryLocationRepository")
* @ORMTable(name="locations")
* @ORMInheritanceType("SINGLE_TABLE")
* @ORMDiscriminatorColumn(name="location_type", type="string")
* @ORMDiscriminatorMap({
"country" = "Country", 
"state" = "State",
"city" = "City"
})
*/
abstract class Location
{
/**
* @var integer
*
* @ORMColumn(name="id", type="integer", precision=0, scale=0, nullable=false, unique=false)
* @ORMId
* @ORMGeneratedValue(strategy="IDENTITY")
*/
protected $id;
/**
* @var string
*
* @ORMColumn(name="name", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $name;
/**
* @var string
*
* @ORMColumn(name="country_code", type="string", length=3, precision=0, scale=0, nullable=true, unique=false)
*/
protected $countryCode;
/**
* @var string
*
* @ORMColumn(name="slug", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $slug;
/**
* @var integer
*
* @ORMColumn(name="geoname_id", type="integer", precision=0, scale=0, nullable=false, unique=false)
*/
protected $geonameId;
/**
* @var string
*
* @ORMColumn(name="lng", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $lng;
/**
* @var string
*
* @ORMColumn(name="lat", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
*/
protected $lat;

/**
* @var DoctrineCommonCollectionsCollection
*
* @ORMOneToMany(targetEntity="BundleLocationBundleEntityLocation", mappedBy="parent")
*/
protected $children;
/**
* @var BundleLocationBundleEntityLocation
*
* @ORMManyToOne(targetEntity="BundleLocationBundleEntityLocation", inversedBy="children")
* @ORMJoinColumns({
*   @ORMJoinColumn(name="parent_id", referencedColumnName="id", nullable=true)
* })
*/
protected $parent;
/**
* @var string
*
* @ORMColumn(name="currency_symbol", type="string", length=100, precision=0, scale=0, nullable=true, unique=false)
*/
protected $currencySymbol;
/* getters and setters*/
}

国家实体

namespace BundleLocationBundleEntity;
use BundleFrameworkBundleEntityDocumentInterface;
use DoctrineORMMapping as ORM;
/**
* Country
*
* @ORMTable(name="locations")
* @ORMEntity(repositoryClass="BundleLocationBundleRepositoryLocationRepository")
*/
class Country extends Location
{
/**
* Set countryCode
*
* @param string $countryCode
* @return Country
*/
public function setCountryCode($countryCode)
{
$this->countryCode = $countryCode;
return $this;
}
/**
* Get countryCode
*
* @return string 
*/
public function getCountryCode()
{
return $this->countryCode;
}
}

州和城市实体就像国家

使用WHERE，您将过滤掉所有不匹配的类别，因此无法计数。应该可以通过一个查询来完成，但必须将条件从WHERE中移动，才能获得不匹配的行，并将其计数为0。简单SQL版本：

SELECT c.*, sum(IF(loc.code = 'x', 1, 0))
FROM category c
LEFT JOIN list l ON c.id = l.c_id
LEFT JOIN loc ON loc.id = l.loc_id
GROUP BY c.id

在条令中没有IF，但你可以使用

CASE WHEN (<cond>) THEN 1 ELSE 0

当您使用$orx expr时。

另一种可能性是将条件移动到像本例中那样的联接，但随后您必须执行任意联接原则来指定自定义ON:

来自文档：任意JOIN语法(from用户u JOIN注释c WITH c.User=u.id)那么你将为这三个相连的表加上三个计数。这是可行的，但很难看。

或@denys281所述的子查询-仅从类别中选择，但select子句包含一列，该列统计每个类别的所有匹配列表。

经过几天对条令中的子查询的研究，我终于得到了适用于我的情况的代码。以下是一些让我重新思考如何编写第一个代码的要点。

  1. 我需要所有类别
  2. 我需要统计每个类别中的所有列表
  3. 仅选择与传入的位置参数相关的列表
  4. 列表具有与位置相关联的多对一

显而易见的解决方案是使用@danys281建议的子查询。

public function findCategoriesWithTotalListing($location)
{
$subQuery = $this->_em->createQueryBuilder();
$subQuery->select('COUNT(l.id)')
->from('BundleAdvertBundleEntityListingItem','l')
->leftJoin('l.category','cat')
->leftJoin('l.country','co')
->leftJoin('l.state','st')
->leftJoin('l.city','ci')
->where('cat.id = c.id');  
if($location instanceOf Country){
$subQuery->andWhere('co.slug = :location');
}
if($location instanceOf State){
$subQuery->andWhere('st.slug = :location');    
}
if($location instanceOf City){
$subQuery->andWhere('ci.slug = :location');
}
$subQuery->andWhere("l.status = :status");

$builder = $this->_em->createQueryBuilder();
$builder->select("c, (".$subQuery->getDql().") AS num")
->from('BundleAdvertBundleEntityCategory', 'c')
->setParameters(['location'=>$location->getSlug(),'status'=>'active'])
->groupBy('c');
return $builder->getQuery()->getResult();
}

谢谢大家给我这个提示。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium