CakePHP - 如何使用查询中关联数据的计算 [英] CakePHP - How to use calculations from associated data in query

问题描述

我想找出最好的方法来做一些事情 - 基本上是在寻找建议之前，我做了长/硬的方式！

我有以下模型关联：

 卖家有多个发票
发票有一个供应商
供应商属于供应商类型
  

每张发票都有一定的金额，并且是从某个日期起。我想要能够找回在过去的完整月份中花费了一定金额的卖家，我们有数据。因此，我需要获取最近发票之前1个月的日期，找出该卖家自该日期起的所有发票上的总金额，然后仅检索总金额介于两者之间的金额，即£10000和£27000（或任何

其次，I想要能够做同样的事情，但是包括SupplierType。因此，用户可能会说他们想要花费在1000英镑到1000英镑之间的卖家。设备供应商£5000，以及1000英镑£7000来自肉类供应商。

我的计划是初步搜索适当的供应商类型id，然后我可以根据每个发票来自适当类型的供应商。

我主要不确定是否有办法计算每月总额，然后在一步中过滤。还是我要在几个步骤做它？我看了Virtual Fields，但我不认为他们做我需要的 - 他们似乎主要用于组合同一记录的字段 - 是正确的吗？

感谢您提供的任何建议。

已填写答案：
感谢那些回答。我在这里发布最终解决方案，以防它帮助任何人：

  SELECT seller_id FROM 
（SELECT i。 seller_id，SUM（price_paid）AS totalamount FROM invoices i 
 JOIN 
（SELECT seller_id，MAX（invoice_date）AS maxdate FROM invoices）sm 
 ON i.seller_id = sm.seller_id 
 WHERE i.invoice_date>（sm.maxdate  -  30）GROUP BY seller_id）t 
 WHERE t.totalamount BETWEEN 0 AND 1000 
  

解决方案

这可以在单个查询中完成，如下所示：

  select * from（
 select seller，sum（amount）as totalamount 
 from invoices i join 
（select seller，max（invoicedate）as maxdate from invoices group by卖家）sm 
 on i.seller = sm.seller 
和i.invoicedate>（sm.maxdate-30）
 group by seller 
）t其中t.totalamount介于1000和50000 
  

I'm trying to figure out the best way to do something - basically looking for advice before I do it the long/hard way!

I have the following model associations:

Seller hasMany Invoices
       Invoice hasOne Supplier
               Supplier belongsTo SupplierType

Each invoice is for a certain amount and is from a certain date. I want to be able to retrieve Sellers who have spent within a certain amount in the past 'full' month for which we have data. So, I need to get the date 1 month before the most recent invoice, find the total on all invoices for that Seller since that date, and then retrieve only those where the total lies between, say, £10000 and £27000 (or whatever range the user has set).

So, any advice on that would be welcome.

Secondly, I want to be able to do the same thing, but with the SupplierType included. So, the user may say that they want Sellers who have spent between £1000 & £5000 from Equipment Suppliers, and between £1000 & £7000 from Meat Suppliers.

My plan here is to do an inital search for the appropriate supplier type id, and then I can filter the invoices based on whether each one is from a supplier of an appropriate type.

I'm mainly not sure whether there is a way to work out the monthly total and then filter on it in one step. Or am I going to have to do it in several steps? I looked at Virtual Fields, but I don't think they do what I need - they seem to be mainly used to combine fields from the same record - is that correct?

Thanks for any advice you can give!

EDITED TO ADD ANSWER: Thanks to those who answered. I'm posting the eventual solution here in case it helps anyone else:

SELECT seller_id FROM 
     (SELECT i.seller_id, SUM(price_paid) AS totalamount FROM invoices i 
     JOIN
     (SELECT seller_id, MAX(invoice_date) AS maxdate FROM invoices) sm
     ON i.seller_id = sm.seller_id
     WHERE i.invoice_date > (sm.maxdate - 30) GROUP BY seller_id) t
WHERE t.totalamount BETWEEN 0 AND 1000

解决方案

This can be done in a single query that will look something like:

select * from (
select seller, sum(amount) as totalamount
from invoices i join
(select seller, max(invoicedate) as maxdate from invoices group by seller) sm
on i.seller=sm.seller
and i.invoicedate>(sm.maxdate-30)
group by seller
) t where t.totalamount between 1000 and 50000

这篇关于CakePHP - 如何使用查询中关联数据的计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！