Android查询多对多关系

发布于 2024-11-02 17:12:01 字数 675 浏览 0 评论 0原文

通过阅读，我了解到实现多对多关系的方法是拥有一个单独的“路由表”。下面是我如何做到这一点的一个示例：

CREATE TABLE Places (_id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE Tags (_id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE RoutingTable_PlacesTags (places_id INTEGER, tag_id INTEGER, FOREIGN KEY(places_id) REFERENCES Places(_id), FOREIGN KEY(tag_id) REFERENCES tags(_id));

在我的数据库帮助程序类中，我想要一个方法，该方法将 Cursor 返回到具有特定标记的 Places 中的所有行，Cursor fetchPlaces(int tagID)。（允许在应用程序内按标签进行过滤）

实施此操作的正确/最佳方法是什么？

我要查询 RoutingTable_PlacesTags，使用返回的 Cursor 构建数组/id 列表对于 Places，然后有另一种方法，我称之为传递返回所有 id 的列表/数组。虽然我知道这会起作用，但我确信一定有一种更干净的方法来做到这一点？

谢谢。

原文

From reading I've understood the way to implement my many-to-many relationship was to have a separate 'Routing Table'. Here's an example of how I've done this:

CREATE TABLE Places (_id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE Tags (_id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE RoutingTable_PlacesTags (places_id INTEGER, tag_id INTEGER, FOREIGN KEY(places_id) REFERENCES Places(_id), FOREIGN KEY(tag_id) REFERENCES tags(_id));

In my database helper class, I want to have a method that returns a Cursor to all rows in Places that have a particular tag, Cursor fetchPlaces(int tagID). (To allow filtering by tag within the app)

What would be the correct/best way to go about implementing this?

I was going to query RoutingTable_PlacesTags, use the returned Cursor to build an array/List of ids for Places, then have another method that I call passing a list/array of id's that returns them all. While I know this would work I'm sure there must be a much cleaner way to do it?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绝不服输 2024-11-09 17:12:01

我认为你可能指的是多对多关系而不是一对多关系。

假设您这样做，您可以使用一条 SQL 语句执行查询：

SELECT name
FROM Places
WHERE EXISTS(
SELECT *
FROM RoutingTable_PlacesTags
WHERE RoutingTable_PlacesTags.places_id = Places._id
AND EXISTS (
SELECT *
FROM Tags
WHERE Tags._id = RoutingTable_PlacesTags.tag_id AND Tags.name = 'Searched for tag name here'
))

I think you might mean many to many relationship instead of one to many.

Assuming you do, you can perform the query using one SQL statement:

SELECT name
FROM Places
WHERE EXISTS(
SELECT *
FROM RoutingTable_PlacesTags
WHERE RoutingTable_PlacesTags.places_id = Places._id
AND EXISTS (
SELECT *
FROM Tags
WHERE Tags._id = RoutingTable_PlacesTags.tag_id AND Tags.name = 'Searched for tag name here'
))

回复收藏 0 原文

~没有更多了~