如何在 DBIx::Class::ResultSet 上执行此搜索和 order_by
问题定义。
我有多个客户和多个用户。每个客户端都需要能够将自定义数据与用户、搜索和排序依据相关联。
数据库解决方案:
定义customfields
表的Customfields
表。它有一个 id
和 name
。 它与Userfields
表(也称为“属性”)具有 has_many 关系。
Userfields
表具有 userid
、customfieldid
、content
和 id
。 它属于Useraccounts
表(又名“useraccount”)和Customfields
(又名“customfield”)
我想要的建议选择语句:
这是一个select 语句可以实现并产生我需要的内容。
SELECT ua.*, (
SELECT content FROM Userfields uf
INNER JOIN Customfields cf
ON cf.id = uf.customfieldid
WHERE cf.name = 'Mothers birthdate'
AND uf.uid=ua.uid
) AS 'Mothers birthdate',
(
SELECT content FROM Userfields uf
INNER JOIN Customfields cf
ON cf.id = uf.customfieldid
WHERE cf.name = 'Join Date' AND
uf.uid=ua.uid
) AS 'Join Date'
FROM UserAccounts ua
ORDER BY 'Mothers birthdate';
在这种情况下,它们可以是 select 语句中 0 ... x 个子 SELECT 语句中的任何内容,并且它们中的任何一个或都不希望被排序。
问题
如何在 DBIx 类结果集上使用 ->search
实现此目的,或者如何使用 ->search< 实现相同的结果/code> 在我的 DBIx-Class 结果集上?
以下是我通常从我的 Useraccounts
表中进行选择的方式,尽管我不确定如何从这里执行我想要的复杂语句。
my @users = $db->resultset('Useraccounts')->search(
undef,
{
page => $page,
join => 'attributes',
...
});
Problem definition.
I have multiple clients with multiple users. Each client needs to be able to associate custom data with a user, search, and order by.
Database Solution:
A table Customfields
which defines the customfields
table. It has an id
and name
.
It has a has_many relationship with a Userfields
table (aka "attributes").
The Userfields
table has a userid
, customfieldid
, content
and id
.
It belongs_to a Useraccounts
table (aka "useraccount") and Customfields
(aka "customfield")
Proposed select statement that I want:
This is a select statement that achieves and produces what I need.
SELECT ua.*, (
SELECT content FROM Userfields uf
INNER JOIN Customfields cf
ON cf.id = uf.customfieldid
WHERE cf.name = 'Mothers birthdate'
AND uf.uid=ua.uid
) AS 'Mothers birthdate',
(
SELECT content FROM Userfields uf
INNER JOIN Customfields cf
ON cf.id = uf.customfieldid
WHERE cf.name = 'Join Date' AND
uf.uid=ua.uid
) AS 'Join Date'
FROM UserAccounts ua
ORDER BY 'Mothers birthdate';
In this case their could be anything from 0 ... x sub SELECT statements in the select statement and any one of them or none of them could be wanting to be ordered by.
Question
How do I achieve this with a ->search
on my DBIx-Class resultset or how do I achieve the same result with a ->search
on my DBIx-Class resultset?
Here is how I usually select from my Useraccounts
table, although I am unsure how to do the complex statement that I want to from here.
my @users = $db->resultset('Useraccounts')->search(
undef,
{
page => $page,
join => 'attributes',
...
});
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这真的很棘手,任何解决方案都不会很漂亮,但如果你稍微改变一下规则,它看起来确实是可能的。请原谅我犯的任何错误,因为我没有去创建一个模式来测试它,但它是基于我拥有的最佳信息(以及 ribasushi 的帮助)。
首先,(假设您的 userfields 表与 customfields 表有一个 own_to 关系,称为
customfield
)uf.uid 匹配的文字
me.uid
是一个未绑定的变量——它是查询中的uid
字段,我们最终会将此查询作为子选择放入其中。默认情况下,DBIC 为查询所寻址的表设置别名;me
;如果你给它一个不同的别名,那么你会在这里使用不同的东西。无论如何,您可以使用任意多个不同的字段重复此
as_query
业务,只需更改字段名称(如果您很聪明,您将编写一个方法来生成它们),然后将它们位于一个数组中,所以现在假设@field_queries
是一个数组,包含上面的$mbd
以及另一个基于 Join Date 的数组,以及您喜欢的任何内容。一旦你有了它,它就像......一样“简单”,
它将包含每个子查询到选择中。
现在来说说令人悲伤的部分:截至目前,整个事情实际上无法正常工作,因为带有占位符的子查询无法正常工作。因此,现在您需要一个额外的解决方法:而不是
'customfield.name' =>; 'Mothersbirthdate'
在子选择搜索中,执行'customfield.name' =>; \q{'Mothersbirthdate'}
-- 这是使用文字 SQL 作为字段名称(这里小心 SQL 注入!),这将避免占位符错误。但在不久的将来,该错误将得到解决,上面的代码将正常工作,我们将更新答案,让您知道情况确实如此。请参阅 DBIx::Class::ResultSource order_by 文档
This is really pretty hairy, and any solution isn't going to be pretty, but it does look to be possible if you bend the rules a little bit. Forgive any mistakes I make, as I didn't go and create a schema to test this on, but it's based on the best info I have (and much help from ribasushi).
First, (assuming that your userfields table has a belongs_to relation with the customfields table, called
customfield
)The literal
me.uid
that uf.uid is being matched against is an unbound variable -- it's theuid
field from the query that we're eventually going to put this query into as a subselect. By default DBIC aliases the table that the query is addressing tome
; if you gave it a different alias then you would use something diferent here.Anyway, You could repeat this
as_query
business with as many different fields as you like, just varying the field-name (if you're smart, you'll write a method to generate them), and put them in an array, so now let's suppose that@field_queries
is an array, containing$mbd
above as well as another one based on Join Date, and anything you like.Once you have that, it's as "simple" as...
which will include each of the subqueries into the select.
Now for the sad part: as of right now, this whole thing actually won't work, because subqueries with placeholders don't work properly. So for now you need an additional workaround: instead of
'customfield.name' => 'Mothers birthdate'
in the subselect search, do'customfield.name' => \q{'Mothers birthdate'}
-- this is using literal SQL for the field name (BE CAREFUL of SQL injection here!), which will sidestep the placeholder bug. But in the not-too-distant future, that bug will be resolved and the code above will work okay, and we'll update the answer to let you know that's the case.See DBIx::Class::ResultSource order_by documentation