Flutter中Supabase外键查询过滤异常问题求助

数据老陈

2026-7-4

问题描述

在Flutter应用中使用Supabase后端进行外键过滤时，其他字段过滤功能正常，但country外键相关过滤完全失效：当widget.countryList传入ID列表时，接口会返回所有job数据——仅符合条件的job会附带country字段信息，不符合条件的job则无country字段，并未实现“只保留符合country条件的job”的预期效果。

问题出在这段代码：

if (widget.countryList != null && widget.countryList!.isNotEmpty) {
  future..in_('city.country', widget.countryList!);
}

问题原因

关联查询默认逻辑问题：Supabase嵌套关联查询默认使用左连接（Left Join），这意味着即使job关联的city/country不匹配过滤条件，job本身依然会被返回，只是关联的city或country字段为null，而非直接过滤掉整个job记录。
过滤路径错误：原代码中in_('city.country', ...)指向的是整个country对象，而非country的主键ID字段，匹配逻辑不符合预期。

解决方案

需要做两处核心修改：

在关联查询中添加!inner关键字，强制使用内连接（Inner Join），确保只有存在匹配city和country的job才会被返回；
调整过滤条件的路径，明确匹配country的ID字段。

修改后的核心代码片段：

// 修改select语句，给city和country添加!inner强制内连接
var future = Supabase.instance.client.from('jobs').select<
        List<Map<String, dynamic>>>(
    '*,city!inner(name_ar,name_en,country!inner(name_en,name_ar,id)),job_category_rel!inner(category(name_en,name_ar))');

// 修改country过滤条件，明确匹配country.id字段
if (widget.countryList != null && widget.countryList!.isNotEmpty) {
  future..in_('city.country.id', widget.countryList!);
}

// 同时修正city过滤的路径，确保匹配city的ID
if (widget.cityList != null && widget.cityList!.isNotEmpty) {
  future..in_('city.id', widget.cityList!);
}

完整修改后的代码

import '/backend/schema/structs/index.dart';
import '/backend/supabase/supabase.dart';
import '/actions/actions.dart' as action_blocks;
import '/flutter_flow/flutter_flow_theme.dart';
import '/flutter_flow/flutter_flow_util.dart';
import '/custom_code/widgets/index.dart'; // Imports other custom widgets
import '/custom_code/actions/index.dart'; // Imports custom actions
import '/flutter_flow/custom_functions.dart'; // Imports custom functions
import 'package:flutter/material.dart';
// Begin custom widget code
// DO NOT REMOVE OR MODIFY THE CODE ABOVE!

import 'package:provider/provider.dart';
import 'package:cached_network_image/cached_network_image.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import 'package:auto_size_text/auto_size_text.dart';

class JobListing extends StatefulWidget {
  const JobListing(
      {Key? key,
      this.width,
      this.height,
      this.countryList,
      this.cityList,
      this.categoryID,
      this.subCatID})
      : super(key: key);

  final double? width;
  final double? height;
  final List<int>? countryList;
  final List<int>? cityList;
  final int? categoryID;
  final int? subCatID;

  @override
  _JobListingState createState() => _JobListingState();
}

class _JobListingState extends State<JobListing> {
  @override
  Widget build(BuildContext context) {
    var future = Supabase.instance.client.from('jobs').select<
            List<Map<String, dynamic>>>(
        '*,city!inner(name_ar,name_en,country!inner(name_en,name_ar,id)),job_category_rel!inner(category(name_en,name_ar))');
    if (widget.countryList != null && widget.countryList!.isNotEmpty) {
      future..in_('city.country.id', widget.countryList!);
    }
    if (widget.cityList != null && widget.cityList!.isNotEmpty) {
      future..in_('city.id', widget.cityList!);
    }
    if (widget.categoryID != null && widget.categoryID != 0) {
      future..eq('job_category_rel.category', widget.categoryID);
    }
    if (widget.subCatID != null && widget.subCatID != 0) {
      future..eq('job_sub_category_rel.sub_category', widget.subCatID);
    }

    return Scaffold(
      body: FutureBuilder<List<Map<String, dynamic>>>(
        future: future,
        builder: (context, snapshot) {
          if (!snapshot.hasData) {
            return const Center(child: CircularProgressIndicator());
          }
          final jobs = snapshot.data!;
          return ListView.builder(
            itemCount: jobs.length,
            itemBuilder: ((context, index) {
              final job = jobs[index];
              String originalDateString = job['created_at'];
              DateTime originalDate = DateTime.parse(originalDateString);
              String formattedDate =
                  DateFormat('dd-MM-yyyy').format(originalDate);
              return GestureDetector(
                onTap: () async {
                  context.pushNamed(
                    'JobDetails',
                    queryParameters: {
                      'jobID': serializeParam(
                        job['id'],
                        ParamType.int,
                      ),
                    }.withoutNulls,
                  );
                },
                child: Padding(
                  padding: EdgeInsetsDirectional.fromSTEB(16.0, 0.0, 16.0, 8.0),
                  child: Container(
                    width: double.infinity,
                    decoration: BoxDecoration(
                      color: FlutterFlowTheme.of(context).secondaryBackground,
                      boxShadow: [
                        BoxShadow(
                          blurRadius: 3.0,
                          color: Color(0x411D2429),
                          offset: Offset(0.0, 1.0),
                        )
                      ],
                      borderRadius: BorderRadius.circular(8.0),
                    ),
                    child: Padding(
                      padding:
                          EdgeInsetsDirectional.fromSTEB(8.0, 8.0, 8.0, 8.0),
                      child: Row(
                        mainAxisSize: MainAxisSize.max,
                        children: [
                          Padding(
                            padding: EdgeInsetsDirectional.fromSTEB(
                                0.0, 1.0, 1.0, 1.0),
                            child: ClipRRect(
                              borderRadius: BorderRadius.circular(6.0),
                              child: CachedNetworkImage(
                                fadeInDuration: Duration(milliseconds: 500),
                                fadeOutDuration: Duration(milliseconds: 500),
                                imageUrl: "${job['image']}",
                                width: 80.0,
                                height: 80.0,
                                fit: BoxFit.cover,
                                errorWidget: (context, error, stackTrace) =>
                                    Image.asset(
                                  'assets/images/error_image.png',
                                  width: 80.0,
                                  height: 80.0,
                                  fit: BoxFit.cover,
                                ),
                              ),
                            ),
                          ),
                          Expanded(
                            child: Padding(
                              padding: EdgeInsetsDirectional.fromSTEB(
                                  8.0, 8.0, 4.0, 0.0),
                              child: Column(
                                mainAxisSize: MainAxisSize.max,
                                mainAxisAlignment: MainAxisAlignment.center,
                                crossAxisAlignment: CrossAxisAlignment.start,
                                children: [
                                  Text(
                                    FFLocalizations.of(context).getVariableText(
                                      enText: job['title_en'],
                                      arText: job['title_ar'],
                                    ),
                                    style: FlutterFlowTheme.of(context)
                                        .headlineSmall,
                                  ),
                                  Padding(
                                    padding: EdgeInsetsDirectional.fromSTEB(
                                        0.0, 4.0, 8.0, 0.0),
                                    child: AutoSizeText(
                                      FFLocalizations.of(context)
                                          .getVariableText(
                                        enText: job['job_category_rel'][0]
                                            ['category']['name_en'],
                                        arText: job['job_category_rel'][0]
                                            ['category']['name_ar'],
                                      ),
                                      textAlign: TextAlign.start,
                                      style: FlutterFlowTheme.of(context)
                                          .labelMedium,
                                    ),
                                  ),
                                ],
                              ),
                            ),
                          ),
                          Column(
                            mainAxisSize: MainAxisSize.max,
                            mainAxisAlignment: MainAxisAlignment.spaceEvenly,
                            children: [
                              DateTime.now().difference(originalDate).inHours <
                                      48
                                  ? Row(children: [
                                      Icon(Icons.star, color: Colors.yellow),
                                      Text("New",
                                          style: TextStyle(fontSize: 10))
                                    ])
                                  : Container(),
                              Text(
                                  FFLocalizations.of(context)
                                      .getVariableText(
                                    enText: job['city']['name_en'],
                                    arText: job['city']['name_ar'],
                                  ),
                                  style: FlutterFlowTheme.of(context)
                                      .bodyMedium,
                                ),
                              Text(formattedDate,
                                  style: TextStyle(fontSize: 11)),
                            ],
                          ),
                        ],
                      ),
                    ),
                  ),
                ),
              );
            }),
          );
        },
      ),
    );
  }
}

补充说明

使用!inner内连接后，只有存在匹配city和country的job才会被返回，从根源上过滤掉不符合条件的记录；
修正city过滤路径为city.id，确保匹配的是city的主键ID，逻辑更严谨；
由于使用内连接，job的city字段不再可能为null，因此移除了原代码中job['city'] == null的判断逻辑。

内容的提问来源于stack exchange，提问作者Ahmed Wagdi

Flutter中Supabase外键查询过滤异常问题求助

补充说明 #

补充说明