如何从PostgreSQL查询数据？基于NestJS、GraphQL、Flutter的需求

一、后端（NestJS + GraphQL + PostgreSQL）实现 #

1. 定义GraphQL返回类型 #

先定义匹配目标数据结构的GraphQL类型，支持两种实现方式：

SDL优先方式

创建GraphQL schema文件：

# src/graphql/collection.graphql
# 单个详情项
type CollectionDetail {
  name: String!
}

# 分组后的集合结构
type GroupedCollection {
  type: String!
  details: [CollectionDetail!]!
}

# 查询入口
type Query {
  getGroupedCollections: [GroupedCollection!]!
}

代码优先方式

用TypeScript装饰器定义实体：

// src/collection/entities/collection.entity.ts
import { ObjectType, Field } from '@nestjs/graphql';

@ObjectType()
export class CollectionDetail {
  @Field()
  name: string;
}

@ObjectType()
export class GroupedCollection {
  @Field()
  type: string;

  @Field(() => [CollectionDetail])
  details: CollectionDetail[];
}

2. 实现分组查询逻辑 #

推荐直接利用PostgreSQL的聚合函数在数据库层面完成分组，效率更高：

编写Service层逻辑

假设数据库表为collection_items，包含type和name字段：

// src/collection/collection.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { CollectionItem } from './entities/collection-item.entity'; // 你的基础实体类
import { GroupedCollection } from './entities/collection.entity';

@Injectable()
export class CollectionService {
  constructor(
    @InjectRepository(CollectionItem)
    private readonly collectionItemRepo: Repository<CollectionItem>,
  ) {}

  async getGroupedCollections(): Promise<GroupedCollection[]> {
    // 直接执行SQL聚合查询
    const result = await this.collectionItemRepo.query(`
      SELECT 
        type,
        JSON_AGG(JSON_BUILD_OBJECT('name', name)) AS details
      FROM collection_items
      GROUP BY type
      ORDER BY type;
    `);
    return result;
  }
}

若偏好TypeORM QueryBuilder风格：

async getGroupedCollections(): Promise<GroupedCollection[]> {
  return this.collectionItemRepo
    .createQueryBuilder('item')
    .select('item.type', 'type')
    .addSelect('JSON_AGG(JSON_BUILD_OBJECT(\'name\', item.name))', 'details')
    .groupBy('item.type')
    .orderBy('item.type')
    .getRawMany();
}

编写Resolver层

将查询暴露给GraphQL接口：

// src/collection/collection.resolver.ts
import { Resolver, Query } from '@nestjs/graphql';
import { CollectionService } from './collection.service';
import { GroupedCollection } from './entities/collection.entity';

@Resolver()
export class CollectionResolver {
  constructor(private readonly collectionService: CollectionService) {}

  @Query(() => [GroupedCollection])
  getGroupedCollections() {
    return this.collectionService.getGroupedCollections();
  }
}

二、前端（Flutter + graphql_flutter）实现 #

1. 定义GraphQL查询文档 #

创建查询文件：

// lib/graphql/queries.dart
import 'package:graphql_flutter/graphql_flutter.dart';

const getGroupedCollectionsQuery = gql('''
  query GetGroupedCollections {
    getGroupedCollections {
      type
      details {
        name
      }
    }
  }
''');

2. 调用查询并渲染数据 #

使用Query组件实现数据获取和UI渲染：

// lib/screens/collection_screen.dart
import 'package:flutter/material.dart';
import 'package:graphql_flutter/graphql_flutter.dart';
import '../graphql/queries.dart';

class CollectionScreen extends StatelessWidget {
  const CollectionScreen({super.key});

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: const Text('分组集合')),
      body: Query(
        options: QueryOptions(document: getGroupedCollectionsQuery),
        builder: (QueryResult result, {VoidCallback? refetch, FetchMore? fetchMore}) {
          // 处理加载状态
          if (result.isLoading) {
            return const Center(child: CircularProgressIndicator());
          }
          // 处理异常
          if (result.hasException) {
            return Center(child: Text('加载失败：${result.exception.toString()}'));
          }
          // 解析数据
          final groupedCollections = result.data?['getGroupedCollections'] as List;
          // 渲染列表
          return ListView.builder(
            itemCount: groupedCollections.length,
            itemBuilder: (context, index) {
              final group = groupedCollections[index];
              return ExpansionTile(
                title: Text(group['type']),
                children: (group['details'] as List).map((detail) {
                  return ListTile(title: Text(detail['name']));
                }).toList(),
              );
            },
          );
        },
      ),
    );
  }
}

若使用Flutter Hooks，代码更简洁：

// lib/screens/collection_screen.dart
import 'package:flutter/material.dart';
import 'package:flutter_hooks/flutter_hooks.dart';
import 'package:graphql_flutter/graphql_flutter.dart';
import '../graphql/queries.dart';

class CollectionScreen extends HookWidget {
  const CollectionScreen({super.key});

  @override
  Widget build(BuildContext context) {
    final queryResult = useQuery(QueryOptions(document: getGroupedCollectionsQuery));

    if (queryResult.isLoading) {
      return const Center(child: CircularProgressIndicator());
    }
    if (queryResult.hasException) {
      return Center(child: Text('加载失败：${queryResult.exception.toString()}'));
    }

    final groupedCollections = queryResult.data?['getGroupedCollections'] as List;

    return Scaffold(
      appBar: AppBar(title: const Text('分组集合')),
      body: ListView.builder(
        itemCount: groupedCollections.length,
        itemBuilder: (context, index) {
          final group = groupedCollections[index];
          return ExpansionTile(
            title: Text(group['type']),
            children: (group['details'] as List).map((detail) {
              return ListTile(title: Text(detail['name']));
            }).toList(),
          );
        },
      ),
    );
  }
}

三、可选扩展 #

• 若需过滤特定类型，可在后端查询中添加参数，比如@Args('allowedTypes') allowedTypes: string[]，并在SQL中加入WHERE type IN (:...allowedTypes)条件
• 若需分页，可结合GraphQL分页规范调整查询和返回结构

内容的提问来源于stack exchange，提问作者Roeurb Navy