在mysql中获取外键用户(Getting users of foreign key in mysql)

在mysql中获取外键用户(Getting users of foreign key in mysql)

我正在玩php以获得一个REST api，我以后可以使用它作为Ember应用程序。 到目前为止一切正常，我刚才有一个关于MySQL的问题。 因为我更像是一个前端人，所以我并不熟悉它。 我的数据库中有一个表，其中一些元素是其他元素的父元素。 所以每个元素都有一个父元素，最顶层的元素有父元素0.现在我希望我的数据库返回元素的所有值以及元素为父元素的元素ID。

我想结合起来

SELECT * FROM table WHERE id=exId

和

SELECT id FROM table WHERE parent_id=exId

我希望将这些孩子作为一个数组返回，所以我可以像{"prop":"val", "children": [1,2,3,4]}那样从中获取JSON。 有没有办法结合这个或者更容易做两个查询并组合数组？

I am playing around with php to get a REST api I can later use for an Ember app. So far everything works fine, I just got a question concerning MySQL. Since I'm more a frontend guy I'm not that familiar with it. I have a table in my database where some elements are parent elements of others. So every element has a parent, the topmost elements have the parent 0. Now i want my database to return all the values of an element and the element ids of which the element is the parent.

I want to combine

SELECT * FROM table WHERE id=exId

and

SELECT id FROM table WHERE parent_id=exId

And i want to return the children as an array so taht i can get JSON out of it like {"prop":"val", "children": [1,2,3,4]}. Is there a way to combine this or is it easier to do two queries and combine the arrays?

最满意答案

加入这两个查询。

SELECT t1.prop, GROUP_CONCAT(t2.id) AS children FROM table AS t1 JOIN table AS t2 ON t1.id = t2.parent_id WHERE t1.id = :exId

children将是一个以逗号分隔的ID列表，您可以使用PHP中的explode将其转换为数组。

Join the two queries.

SELECT t1.prop, GROUP_CONCAT(t2.id) AS children FROM table AS t1 JOIN table AS t2 ON t1.id = t2.parent_id WHERE t1.id = :exId

children will be be a comma-separated list of IDs, which you can turn into an array using explode in PHP.