获取一对多关系中的所有子记录 [英] Getting all sub records in a one-to-many relationship

问题描述

我正在为我的应用程序运行查询，这真的让我希望我使用ORM。我的表格结构如下：

ul>

id

姓名

排序

字段

• id




• tabid

字段和制表符之间的一对多关系。我想做的是，如果可能，使用纯SQL，创建一个查询，其中有选项卡，并在每个选项卡下面显示所有字段的子查询。

只是做以下，但我想知道是否有更好的事情。

 < cfquery name =local.tabQuery attributeCollection =＃Variables.dsn＃> 
 SELECT id，name FROM tabs ORDER BY sort 
< / cfquery> 
< cfset local.tabs = [] /> 
< cfloop query =local.tabQuery> 
< cfquery name =local.fieldsattributeCollection =＃Variables.dsn3> 
 SELECT * FROM fields WHERE tabid =< cfqueryparam value =＃local.tabQuery.id＃cfsqltype =cf_sql_integer/> 
< cfquery> 
< cfset arrayAppend（local.tabs，local.fields）/> 
< / cfloop> 
  

注意：这不是我的实际代码，但理论上，罚款。

解决方案

 < cfquery name =local.tabQueryattributeCollection =＃Variables.dsn＃> 
 SELECT t.id，t.name，t.sort，f.id AS fieldID，f.label 
 FROM tabs t INNER JOIN字段f ON t.id = f.tabID 
 ORDER BY t.sort 
< / cfquery> 
 
< cfoutput query =local.tabQuerygroup =sort> 
 Tab：＃local.tabQuery.name＃< br> 
 
< cfoutput> 
字段：＃local.tabQuery.label＃< br> 
< / cfoutput> 
< / cfoutput> 
  

I am running a query for my application, that is really making me wish I used ORM. My table are structured as follows:

tabs

• id
• name
• sort

fields

• id
• label
• tabid

As you can assume there is a one-to-many relationship between fields and tabs. What I would like to do is, using pure SQL if possible, create a query that has the tabs and underneath each tab shows a subquery of all fields.

Currently I am just doing the following, but I was wondering if there is something better to do.

<cfquery name="local.tabQuery" attributeCollection="#Variables.dsn#">
     SELECT id,name FROM tabs ORDER BY sort
</cfquery>
<cfset local.tabs = [] />
<cfloop query="local.tabQuery">
     <cfquery name="local.fields" attributeCollection="#Variables.dsn3">
          SELECT * FROM fields WHERE tabid = <cfqueryparam value="#local.tabQuery.id#" cfsqltype="cf_sql_integer" />
     <cfquery>
     <cfset arrayAppend(local.tabs, local.fields) />
</cfloop>

Note: That is not my actual code, but that should, in theory, work just fine.

解决方案

You want grouped output.

<cfquery name="local.tabQuery" attributeCollection="#Variables.dsn#">
    SELECT t.id, t.name, t.sort, f.id AS fieldID, f.label
    FROM tabs t  INNER JOIN fields f ON t.id = f.tabID
    ORDER BY t.sort
</cfquery>

<cfoutput query="local.tabQuery" group="sort">
    Tab: #local.tabQuery.name#<br>

    <cfoutput>
        Field: #local.tabQuery.label#<br>
    </cfoutput>
</cfoutput>

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