从2个表中获取记录数-一对多关系 [英] getting number of records from 2 tables - one to many relationship

问题描述

我在搜索查询时遇到了问题，我必须即时构建该查询才能从数据库返回记录.

I have problem with search query that i have to built on the fly to return records from the database.

我有2个表:adds and adds_filters`.为了简单起见，我使表的添加比实际短，删除了一些(不相关的)字段
我的表结构:

I have 2 tables: adds andadds_filters`. For the sake of simplicity, i make the table adds shorter than it is, removing some of the (irrelevant) fields
My table structure:

CREATE TABLE IF NOT EXISTS `adds` (
`addid` int(11) NOT NULL AUTO_INCREMENT,
`memberid` int(11) NOT NULL,
`isnew` int(11) NOT NULL,
`catid` int(11) NOT NULL,
`manufacturerid` int(11) NOT NULL,
`modelid` varchar(255) DEFAULT NULL,
`colorid` int(11) DEFAULT NULL,
`geographicareaid` int(45) NOT NULL,
`addtypeid` varchar(45) NOT NULL,
`addcreatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`addvalidfrom` date NOT NULL,
`addvaliduntil` date NOT NULL,
`addcreatedfromip` varchar(255) NOT NULL,
`yearofmanufacturing` varchar(255) DEFAULT NULL,
`monthofmanufacturing` int(11) DEFAULT NULL,
`hoursused` int(11) DEFAULT NULL,
`cc2` int(11) DEFAULT NULL,
`horsepowers` int(11) DEFAULT NULL,
`metalic` tinyint(4) DEFAULT NULL,
`isdamaged` tinyint(4) DEFAULT NULL,
`price` float DEFAULT NULL,
`hasvat` tinyint(4) NOT NULL,
`canbenegotiated` tinyint(4) DEFAULT NULL,
`addtitle` varchar(255) DEFAULT NULL,
`addtext` text NOT NULL,
`youtubevideo` varchar(255) DEFAULT NULL,
`visible` tinyint(4) DEFAULT NULL,
`ff1` varchar(255) DEFAULT NULL,
`ff2` varchar(255) DEFAULT NULL,
`ff3` varchar(255) DEFAULT NULL,
`ff4` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`addid`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=43 ;


CREATE TABLE IF NOT EXISTS `adds_filters` (
`addfilterid` int(11) NOT NULL AUTO_INCREMENT,
`addid` int(11) NOT NULL,
`filterid` int(11) NOT NULL,
 PRIMARY KEY (`addfilterid`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=45 ;

这是小提琴

问题是用户可以为每个添加项添加多个过滤器，即 车辆具有AC，GPS，可移动座舱等...此数据存储在adds_filters中.因此，一个添加项可以包含许多过滤器.

Problem is that user can add more than one filter for each adds, that is if the vehicle has AC,GPS, removable cabin and so on...This data is stored in the adds_filters. So, one add can have many filters.

当用户选择catid为1，manufacturerid为1，然后用户检查具有ID的过滤器时，查询的外观如何 例如67和158?

How the query should look like when user choose that catid is 1, manufacturerid is 1 and then users check filters with ids 67 and 158 for example?

与查询相比，我更喜欢视图，但是我不知道如何构建这样的视图.之所以喜欢这种观点，是因为在这种情况下，我将能够使用select *而不是复杂的查询...

I would prefer view over the query, but i have no idea how can i build such view. Reason from preferring the view is that in such a case, i will be able to use select * instead of complicated queries...

任何帮助将不胜感激.

推荐答案

使用此表结构，如果要显示filterid，则将需要为每个选中的filterid提供子查询.
在不显示的情况下，您只能使用DISTINCT和IN.

With this table structure, you gonna need subqueries for each checked filterid, if you want filterids to be displayed.
Without displaying, you can just use DISTINCT and IN.

这是不显示filterid s

SELECT DISTINCT a.*
          FROM adds a
               JOIN adds_filters f
                    ON a.`addid` = f.`addid`
         WHERE      a.`catid` = 1
               AND  a.`manufacturerid` = 1
               AND  f.`filterid` IN (67, 158);

此处是查询，显示了两个不同的filterid s :

SELECT t1.*, t2.filterid as filterid2
FROM
(
SELECT DISTINCT a.*,
       f.`filterid`
          FROM adds a
               JOIN adds_filters f
                    ON a.`addid` = f.`addid`
         WHERE      a.`catid` = 1
               AND  a.`manufacturerid` = 1
               AND  f.`filterid` = 67
) t1

JOIN 

(
SELECT DISTINCT a.`addid`,
       f.`filterid`
          FROM adds a
               JOIN adds_filters f
                    ON a.`addid` = f.`addid`
         WHERE      a.`catid` = 1
               AND  a.`manufacturerid` = 1
               AND  f.`filterid` = 158

  ) t2
ON t1.addid = t2.addid;

这篇关于从2个表中获取记录数-一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！