php - 通过 SQL 或 PHP 对多个表进行递归 SELECT

Question

我有一个房地产项目，其表格如下:

1. apartment 表具有建筑物 ID
2. building 表有一个邻居 ID
3. neighborhood 表具有城市 ID

现在如果我想获得某个城市的所有公寓，最好的方法是什么？我正在使用 PHP 和 MySQL，这是我尝试过但失败的

public static function getAllApartmentsInACity ($city_id) {

    $results = array();
    $db = DB::getInstance(); //My DB class nothing wrong here
    $n = self::getAllNeighborhoodsInACity($city_id); // returns an array of objects of all neighborhoods
    $b = array();
    $a = array();

    foreach ($n as $singleN) {
        $b[] = self::getAllBuildingsInANeighborhood($singleN->id); //returns an array of objects of all buildings
    }

    foreach ($b as $singleB) {
        echo $singleB->id . '-';
    }

    return $a;

}

毕竟，当我从这个函数返回var_dump时，我得到了Null，前提是我在表中有数据。任何帮助将不胜感激

Answer 1

使用以下查询:

$query = "SELECT apartment.*
          FROM apartment
             JOIN building ON
                 building.id = apartment.building_id
             JOIN neighborhood ON
                 neighborhood.id = building.neighborhood_id
             JOIN city ON
                 city.id = neighborhood.city_id AND
                 city.id = 'SOME CITY ID'
         ";