在Core Data中写入本机SQL [英] Write native SQL in Core Data

问题描述

我需要写一个本地SQL查询，而我在我的项目中使用Core Data。我真的需要这样做，因为我现在使用 NSPredicate 它不够高效（在一个单一的情况下）。我只需要写几个子查询和连接来获取大量的行，并通过一个特殊的字段进行排序。特别是，我需要通过他们的孩子的价值的总和来排序。现在我使用NSPredicate获取一切，然后我手动排序我的结果（数组），但这只是需要太长时间，因为有成千上万的结果。

I need to write a native SQL Query while I'm using Core Data in my project. I really need to do that, since I'm using NSPredicate right now and it's not efficient enough (in just one single case). I just need to write a couple of subqueries and joins to fetch a big number of rows and sort them by a special field. In particular, I need to sort it by the sum of values of their child-entites. Right now I'm fetching everything using NSPredicate and then I'm sorting my result (array) manually, but this just takes too long since there are many thousands of results.

如果我错了，请更正我，但我确定这不是一个巨大的挑战，因为在iOS应用程序中有一种使用sqlite的方法。

Please correct me if I'm wrong, but I'm pretty sure this can't be a huge challenge, since there's a way of using sqlite in iOS applications.

如果有人可以引导我走向正确的方向，这将是真棒。
提前感谢。

It would be awesome if someone could guide me into the right direction. Thanks in advance.

编辑：
让我解释一下我在做什么。
这是我的Coredata模型：

Let me explain what I'm doing. Here's my Coredata model:

这里是我的结果在iPad上的显示：

And here's how my result looks on the iPad:

我显示一个表，每个客户有一行，每个客户都有他从1月到6月的销售额2012（最后）和2013（Curr）。在 Curr 旁边有这两个值之间的差异。

I'm showing a table with one row per customer, where every customer has an amount of sales he made from January to June 2012 (Last) AND 2013 (Curr). Next to the Curr there's the variance between those two values. The same thing for gross margin and coverage ratio.

每个客户都保存在 Kunde 表格中，每个 Kunde 有几个 PbsRow 。 PbsRow 实际上持有每月销售金额的总和。

Every customer is saved in the Kunde table and every Kunde has a couple of PbsRows. PbsRow actually holds the sum of sales amounts per month.

所以我在做什么，结果，是获取2013年1月至6月之间的所有 PbsRows ，然后执行以下操作：

So what I'm doing in order to show these results, is to fetch all the PbsRows between January and June 2013 and then do this:

self.kunden = [NSMutableOrderedSet orderedSetWithArray:[pbsRows valueForKeyPath:@"kunde"]];

现在我有所有客户（Kunde），在2013年1月到6月之间有记录。
然后我使用for循环来计算每个客户的总和。

Now I have all customers (Kunde) which have records between January and June 2013. Then I'm using a for loop to calculate the sum for each single customer.

这个想法是获取当前年度的销售额，并将它们与去年。
坏事是有很多客户， for -loop只需要很长时间： - （

The idea is to get the amounts of sales of the current year and compare them to the last year. The bad thing is that there are a lot of customers and the for-loop just takes very long :-(

推荐答案

这是一个黑客，但... SQLite库能够在给定的时间打开多个数据库文件。这将是相当可行的直接使用SQLite打开Core Data DB文件（只读/使用），并打开第二个文件（报告/临时表），然后可以对Core Data DB中的数据执行直接SQL查询，并将它们持久化第二个文件（如果需要持久化）。

This is a bit of a hack, but... The SQLite library is capable of opening more than one database file at a given time. It would be quite feasible to open the Core Data DB file (read/only usage) directly with SQLite and open a second file in conjunction with this (reporting/temporary tables). One could then execute direct SQL queries on the data in the Core Data DB and persist them into a second file (if persistence is needed).

我做了这样的事情几次。SQLite库中有一些功能（例如：full-text搜索引擎），不通过Core Data公开。

I have done this sort of thing a few times. There are features available in the SQLite library (example: full-text search engine) that are not exposed through Core Data.

这篇关于在Core Data中写入本机SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！