請教，Laravel 多對多怎麼查詢同時符合兩個條件的資料

這邊先附上資料庫的規劃

stations(車站表)

id	name(車站名)	sorts(順序)
1	台北車站	1
2	桃園車站	2
3	台中車站	3
4	高雄車站	4

trains(車班表)

註記:train_type_id這次提問不會用到，但是實質是有的，所以也加進來顯示

id	train_type_id(關聯車種id)	work_date(開車日期)
1	3	2022-09-30
2	3	2022-09-30
3	1	2022-09-30
4	5	2022-09-30

stop_ats(停靠站)

id	station_id(關聯車站id)	train_id(關聯車班id)	到站時間	離站時間	方向:0=順向，1=逆向
1	1	1	10:00:00	10:01:00	0
2	2	1	10:05:00	10:06:00	0
3	3	1	10:10:00	10:11:00	0
4	5	1	10:15:00	10:16:00	0
5	2	2	10:00:00	10:01:00	0
6	5	2	10:10:00	10:11:00	0
7	3	3	10:00:00	10:01:00	1
8	1	3	10:10:00	10:11:00	1

stations跟trains的Model有互相設定關聯belongsToMany

// Train的Molde
public function stations()
{
    return $this->belongsToMany(Station::class, 'stop_ats', 'train_id', 'station_id')
        ->withPivot('direction', 'arrival_time', 'departure_time')
        ->withTimestamps();
}

// stations的Model
public function trains()
{
    return $this->belongsToMany(Train::class, 'stop_ats', 'station_id', 'train_id')
        ->withPivot('direction', 'arrival_time', 'departure_time')
        ->withTimestamps();
}

需求

查詢畫面

請教，Laravel 多對多怎麼查詢同時符合兩個條件的資料

昨天試了一天，總共搞出兩個寫法，但是還是覺得怪怪的，想上來問問，這個搜尋的正確解法應該用什麼呢?

第一種

參考版上其他人的方式寫的網址在這裡
我先找出出發站跟抵達站，為了的是判斷他是順向還是逆向
然後找出這兩個車站個別有停靠的車班id，再取出重複的id
接著就是我組成的方式

/**
 * 簡單易懂方法
 */
// 出發站
$start_station = Station::find($request->start_station);
// 抵達站
$end_station = Station::find($request->end_station);
// 判斷是順向還是逆向
$direction = ($start_station->sorts < $end_station->sorts ? 0 : 1);

// 取得出發站的停靠車班id
$start_station_stop = $start_station->trains->pluck('id')->toArray();
// 取得抵達站的停靠車班id
$end_station_stop = $end_station->trains->pluck('id')->toArray();
// 取出重合的id
$trains_id =  array_intersect($start_station_stop, $end_station_stop);

// 建立查詢建構器，分段方式撰寫SQL語句
$query = Train::query();
// 篩選日期
$query->where('work_date', '=', $request->work_date);
// 指定id
$query->whereIn('id', $trains_id);
// 指定順向逆向
$query->whereHas('stations', function ($query) use ($direction) {
    $query->where('direction', '=', $direction);
});
// 關聯停靠站&車種
$query->with(['stations' => function ($query) {
    // 排列順序
    $query->orderBy('arrival_time', 'asc')->orderBy('id', 'desc');
}, 'trainType']);

結果長這樣

[{
    "id": 2,
    "status": 1,
    "train_type_id": 3,
    "work_date": "2022-09-30",
    "created_at": "2022-09-30t02:27:12.000000z",
    "updated_at": "2022-09-30t02:27:12.000000z",
    "stations": [{
        "id": 15,
        "status": 1,
        "name": "彰化",
        "sorts": 15,
        "created_at": "2022-09-30t02:22:51.000000z",
        "updated_at": "2022-09-30t02:22:51.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 15,
            "direction": 1,
            "arrival_time": "10:00:00",
            "departure_time": "10:00:59",
            "created_at": "2022-09-30t02:34:31.000000z",
            "updated_at": "2022-09-30t02:34:31.000000z"
        }
    }, {
        "id": 9,
        "status": 1,
        "name": "台中",
        "sorts": 9,
        "created_at": "2022-09-30t02:22:14.000000z",
        "updated_at": "2022-09-30t02:22:14.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 9,
            "direction": 1,
            "arrival_time": "10:30:00",
            "departure_time": "10:30:59",
            "created_at": "2022-09-30t02:34:55.000000z",
            "updated_at": "2022-09-30t02:34:55.000000z"
        }
    }, {
        "id": 4,
        "status": 1,
        "name": "潭子",
        "sorts": 4,
        "created_at": "2022-09-30t02:21:35.000000z",
        "updated_at": "2022-09-30t02:21:35.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 4,
            "direction": 1,
            "arrival_time": "11:00:00",
            "departure_time": "11:00:59",
            "created_at": "2022-09-30t02:35:30.000000z",
            "updated_at": "2022-09-30t02:35:30.000000z"
        }
    }, {
        "id": 2,
        "status": 1,
        "name": "豐原",
        "sorts": 2,
        "created_at": "2022-09-30t02:21:16.000000z",
        "updated_at": "2022-09-30t02:21:16.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 2,
            "direction": 1,
            "arrival_time": "11:30:00",
            "departure_time": "11:30:59",
            "created_at": "2022-09-30t02:35:45.000000z",
            "updated_at": "2022-09-30t02:35:45.000000z"
        }
    }, {
        "id": 1,
        "status": 1,
        "name": "后里",
        "sorts": 1,
        "created_at": "2022-09-30t02:21:10.000000z",
        "updated_at": "2022-09-30t02:21:10.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 1,
            "direction": 1,
            "arrival_time": "12:00:00",
            "departure_time": "12:00:59",
            "created_at": "2022-09-30t02:36:06.000000z",
            "updated_at": "2022-09-30t02:36:06.000000z"
        }
    }],
    "train_type": {
        "id": 3,
        "status": 1,
        "name": "自強號",
        "price": 20,
        "seats": 150,
        "created_at": "2022-09-23t00:09:03.000000z",
        "updated_at": "2022-09-23t00:09:03.000000z"
    }
}]

第二種

我是依照這個網站的方式去組成的SQL，但是我有看沒有懂

/**
 * 用構造器組成的
 */
// 出發站
$start_station = Station::find($request->start_station);
// 抵達站
$end_station = Station::find($request->end_station);
// 判斷是順向還是逆向
$direction = ($start_station->sorts < $end_station->sorts ? 0 : 1);

$query = train::query();
// 只查車班資料
$query->select('trains.*');
$query->join('stop_ats as a', 'a.train_id', '=', 'trains.id');
$query->join('stop_ats as b', 'a.train_id', '=', 'b.train_id');
// 指定出發站
$query->where('a.station_id', '=', $request->start_station);
// 指定抵達站
$query->where('b.station_id', '=', $request->end_station);
// 指定日期
$query->where('trains.work_date', '=', $request->work_date);
// 指定順向逆向
$query->where('a.direction', '=', $direction);
// 關聯停靠站&車種
$query->with(['stations' => function ($query) {
    // 排列順序
    $query->orderBy('arrival_time', 'asc')->orderBy('id', 'desc');
}, 'trainType']);

結果也是長這樣

[{
    "id": 2,
    "status": 1,
    "train_type_id": 3,
    "work_date": "2022-09-30",
    "created_at": "2022-09-30t02:27:12.000000z",
    "updated_at": "2022-09-30t02:27:12.000000z",
    "stations": [{
        "id": 15,
        "status": 1,
        "name": "彰化",
        "sorts": 15,
        "created_at": "2022-09-30t02:22:51.000000z",
        "updated_at": "2022-09-30t02:22:51.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 15,
            "direction": 1,
            "arrival_time": "10:00:00",
            "departure_time": "10:00:59",
            "created_at": "2022-09-30t02:34:31.000000z",
            "updated_at": "2022-09-30t02:34:31.000000z"
        }
    }, {
        "id": 9,
        "status": 1,
        "name": "台中",
        "sorts": 9,
        "created_at": "2022-09-30t02:22:14.000000z",
        "updated_at": "2022-09-30t02:22:14.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 9,
            "direction": 1,
            "arrival_time": "10:30:00",
            "departure_time": "10:30:59",
            "created_at": "2022-09-30t02:34:55.000000z",
            "updated_at": "2022-09-30t02:34:55.000000z"
        }
    }, {
        "id": 4,
        "status": 1,
        "name": "潭子",
        "sorts": 4,
        "created_at": "2022-09-30t02:21:35.000000z",
        "updated_at": "2022-09-30t02:21:35.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 4,
            "direction": 1,
            "arrival_time": "11:00:00",
            "departure_time": "11:00:59",
            "created_at": "2022-09-30t02:35:30.000000z",
            "updated_at": "2022-09-30t02:35:30.000000z"
        }
    }, {
        "id": 2,
        "status": 1,
        "name": "豐原",
        "sorts": 2,
        "created_at": "2022-09-30t02:21:16.000000z",
        "updated_at": "2022-09-30t02:21:16.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 2,
            "direction": 1,
            "arrival_time": "11:30:00",
            "departure_time": "11:30:59",
            "created_at": "2022-09-30t02:35:45.000000z",
            "updated_at": "2022-09-30t02:35:45.000000z"
        }
    }, {
        "id": 1,
        "status": 1,
        "name": "后里",
        "sorts": 1,
        "created_at": "2022-09-30t02:21:10.000000z",
        "updated_at": "2022-09-30t02:21:10.000000z",
        "pivot": {
            "train_id": 2,
            "station_id": 1,
            "direction": 1,
            "arrival_time": "12:00:00",
            "departure_time": "12:00:59",
            "created_at": "2022-09-30t02:36:06.000000z",
            "updated_at": "2022-09-30t02:36:06.000000z"
        }
    }],
    "train_type": {
        "id": 3,
        "status": 1,
        "name": "自強號",
        "price": 20,
        "seats": 150,
        "created_at": "2022-09-23t00:09:03.000000z",
        "updated_at": "2022-09-23t00:09:03.000000z"
    }
}]

ORM還在學習中，想請教有更好的解決方法嗎?

yuyuan_zhou

1 声望

暂无个人描述~

0 人点赞

你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程，JWT 概念及使用和 API 开发相关的进阶知识。

从零开始带你一步步开发一个 Go 博客项目，让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。

查询

日期	出发站	抵达站
2022-09-30	桃園車站	高雄車站

结果

火车类型	出发时间	抵达时间
2	10:00:00	10:10:00
3	10:05:00	10:15:00

SQLite 实现

WITH
  request(work_date, start_station, end_station) AS (
    VALUES ('2022-09-30', '桃園車站', '高雄車站')
  ),

  stations(id, name, sorts) AS (
    VALUES
      (1, '台北車站', 1),
      (2, '桃園車站', 2),
      (3, '台中車站', 3),
      (5, '高雄車站', 4)  -- ID 修改为 5
  ),

  trains(id, train_type_id, work_date) AS (
    VALUES
      (1, 3, '2022-09-30'),
      (2, 2, '2022-09-30'),  -- train_type_id 修改为 2
      (3, 1, '2022-09-30'),
      (4, 5, '2022-09-30')
  ),

  stop_ats(station_id, train_id, arrival_time, departure_time, direction) AS (
    VALUES
      (1, 1, '10:00:00', '10:01:00', 0),
      (2, 1, '10:05:00', '10:06:00', 0),
      (3, 1, '10:10:00', '10:11:00', 0),
      (5, 1, '10:15:00', '10:16:00', 0),
      (2, 2, '10:00:00', '10:01:00', 0),
      (5, 2, '10:10:00', '10:11:00', 0),
      (3, 3, '10:00:00', '10:01:00', 1),
      (1, 3, '10:10:00', '10:11:00', 1)
  ),

  query(work_date, start_station_id, end_station_id, direction) AS (
    SELECT request.work_date, start.id, end.id, start.sorts > end.sorts
      FROM (SELECT id, sorts FROM request, stations WHERE name = start_station) start,
           (SELECT id, sorts FROM request, stations WHERE name = end_station) end,
           request
  )

SELECT t.train_type_id '火车类型', a.arrival_time '出发时间', b.arrival_time '抵达时间'
  FROM query q
  JOIN stop_ats a ON (a.station_id, a.direction) = (q.start_station_id, q.direction)
  JOIN stop_ats b ON (b.station_id, b.train_id, b.direction) = (q.end_station_id, a.train_id, q.direction)
  JOIN trains t ON (t.id, t.work_date) = (b.train_id, q.work_date)
 ORDER BY a.arrival_time;

1年前评论

臭鼬

见习助教 167 声望

awobaz/compoships 搜一下

1年前评论

Colorado

课程读者 133 声望

你是湾湾同胞吗？

1年前评论

yuyuan_zhou （楼主）

是的

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

請教，Laravel 多對多怎麼查詢同時符合兩個條件的資料

stations(車站表)

trains(車班表)

stop_ats(停靠站)

需求

第一種

第二種

推荐文章：

查询

结果

SQLite 实现

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

請教，Laravel 多對多怎麼查詢同時符合兩個條件的資料

stations(車站表)

trains(車班表)

stop_ats(停靠站)

需求

第一種

第二種

推荐文章：

查询

结果

SQLite 实现

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录