how to do a query filter (foreign key) for supabase in flutter

Question

I'm trying to do a query filtering using a foreign key from my supabase backend to my flutter app. It works with all fields but only this one depends on forgien key filters are not working, here is my code :

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(name_ar,name_en,country(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', widget.countryList!);
    }
    if (widget.cityList != null && widget.cityList!.isNotEmpty) {
      future..in_('city', 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(),
                              job['city'] == null
                                  ? 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)),
                            ],
                          ),
                        ],
                      ),
                    ),
                  ),
                ),
              );
            }),
          );
        },
      ),
    );
  }
}

the point where the problem happens is :

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

when there are IDs in widget.countryList it returns all data,, but only returns country information for the ID included in the list .. other data are being returned too but without country field data in it ..!!

Answer 1

You need to make the country table inner joined as well if you want to filter the parent table with it.

country!inner(name_en,name_ar,id)