多集合查询 #

一、多集合查询概述 #

ArangoDB支持多种方式关联多个集合的数据，类似于关系数据库的JOIN操作。

1.1 关联方式 #

方式	说明
嵌套FOR	最常用的关联方式
子查询	灵活的关联方式
图遍历	基于边集合的关联

1.2 与SQL JOIN对比 #

SQL	AQL
INNER JOIN	嵌套FOR + FILTER
LEFT JOIN	嵌套FOR + 子查询
多表JOIN	多层嵌套FOR

二、嵌套FOR关联 #

2.1 基本关联 #

aql

FOR order IN orders
    FOR user IN users
        FILTER order.userId == user._key
        RETURN {
            orderId: order._key,
            userName: user.name,
            amount: order.amount
        }

2.2 三表关联 #

aql

FOR order IN orders
    FOR user IN users
        FILTER order.userId == user._key
        FOR product IN products
            FILTER order.productId == product._key
            RETURN {
                orderId: order._key,
                userName: user.name,
                productName: product.name,
                amount: order.amount
            }

2.3 多条件关联 #

aql

FOR order IN orders
    FOR user IN users
        FILTER order.userId == user._key
        AND user.status == "active"
        AND order.status == "completed"
        RETURN {
            orderId: order._key,
            userName: user.name,
            amount: order.amount
        }

2.4 关联后过滤 #

aql

FOR order IN orders
    FOR user IN users
        FILTER order.userId == user._key
        FILTER order.amount > 1000
        FILTER user.city == "北京"
        RETURN {
            orderId: order._key,
            userName: user.name,
            amount: order.amount
        }

三、子查询关联 #

3.1 基本子查询关联 #

aql

FOR order IN orders
    LET user = (
        FOR u IN users
            FILTER u._key == order.userId
            RETURN u
    )[0]
    RETURN {
        orderId: order._key,
        userName: user.name,
        amount: order.amount
    }

3.2 左连接效果 #

aql

FOR user IN users
    LET orders = (
        FOR order IN orders
            FILTER order.userId == user._key
            RETURN order
    )
    RETURN {
        user: user.name,
        orders: orders,
        orderCount: LENGTH(orders)
    }

3.3 一对多关联 #

aql

FOR user IN users
    LET userOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            RETURN {
                orderId: order._key,
                amount: order.amount,
                createdAt: order.createdAt
            }
    )
    RETURN {
        userId: user._key,
        userName: user.name,
        orders: userOrders,
        totalOrders: LENGTH(userOrders),
        totalAmount: SUM(userOrders[*].amount)
    }

3.4 多对多关联 #

aql

FOR student IN students
    LET courses = (
        FOR enrollment IN enrollments
            FILTER enrollment.studentId == student._key
            FOR course IN courses
                FILTER course._key == enrollment.courseId
                RETURN {
                    courseId: course._key,
                    courseName: course.name
                }
    )
    RETURN {
        studentId: student._key,
        studentName: student.name,
        courses: courses
    }

四、DOCUMENT函数 #

4.1 单文档关联 #

aql

FOR order IN orders
    LET user = DOCUMENT("users", order.userId)
    RETURN {
        orderId: order._key,
        userName: user.name,
        amount: order.amount
    }

4.2 使用_id关联 #

aql

FOR order IN orders
    LET user = DOCUMENT(order.userId)
    RETURN {
        orderId: order._key,
        userName: user ? user.name : "未知",
        amount: order.amount
    }

4.3 多文档关联 #

aql

FOR order IN orders
    LET user = DOCUMENT("users", order.userId)
    LET product = DOCUMENT("products", order.productId)
    RETURN {
        orderId: order._key,
        userName: user.name,
        productName: product.name,
        amount: order.amount
    }

五、关联聚合 #

5.1 关联统计 #

aql

FOR user IN users
    LET orderStats = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT AGGREGATE
                totalAmount = SUM(order.amount),
                orderCount = COUNT(),
                avgAmount = AVG(order.amount)
            RETURN { totalAmount, orderCount, avgAmount }
    )[0]
    RETURN {
        userId: user._key,
        userName: user.name,
        stats: orderStats || { totalAmount: 0, orderCount: 0, avgAmount: 0 }
    }

5.2 分组关联统计 #

aql

FOR user IN users
    LET cityOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT city = order.deliveryCity AGGREGATE
                total = SUM(order.amount),
                count = COUNT()
            RETURN { city, total, count }
    )
    RETURN {
        userName: user.name,
        cityOrders: cityOrders
    }

5.3 关联排名 #

aql

FOR user IN users
    LET totalSpent = (
        FOR order IN orders
            FILTER order.userId == user._key
            AGGREGATE total = SUM(order.amount)
            RETURN total
    )[0] || 0
    RETURN {
        userId: user._key,
        userName: user.name,
        totalSpent: totalSpent
    }
SORT totalSpent DESC
LIMIT 10

六、复杂关联场景 #

6.1 层级关联 #

aql

FOR category IN categories
    FILTER category.parentId == null
    LET subCategories = (
        FOR sub IN categories
            FILTER sub.parentId == category._key
            LET products = (
                FOR product IN products
                    FILTER product.categoryId == sub._key
                    RETURN product
            )
            RETURN {
                name: sub.name,
                productCount: LENGTH(products),
                products: products
            }
    )
    RETURN {
        category: category.name,
        subCategories: subCategories
    }

6.2 时间范围关联 #

aql

FOR user IN users
    LET recentOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            AND order.createdAt > DATE_SUBTRACT(DATE_NOW(), 30, "days")
            RETURN order
    )
    LET oldOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            AND order.createdAt <= DATE_SUBTRACT(DATE_NOW(), 30, "days")
            RETURN order
    )
    RETURN {
        userName: user.name,
        recentOrderCount: LENGTH(recentOrders),
        oldOrderCount: LENGTH(oldOrders)
    }

6.3 条件关联 #

aql

FOR order IN orders
    LET reviewer = order.reviewId ? (
        FOR review IN reviews
            FILTER review._key == order.reviewId
            FOR user IN users
                FILTER user._key == review.userId
                RETURN user
    )[0] : null
    RETURN {
        orderId: order._key,
        hasReview: order.reviewId != null,
        reviewerName: reviewer ? reviewer.name : null
    }

6.4 自关联 #

aql

FOR employee IN employees
    LET manager = (
        FOR e IN employees
            FILTER e._key == employee.managerId
            RETURN e
    )[0]
    LET subordinates = (
        FOR e IN employees
            FILTER e.managerId == employee._key
            RETURN e
    )
    RETURN {
        employeeName: employee.name,
        managerName: manager ? manager.name : null,
        subordinateCount: LENGTH(subordinates),
        subordinates: subordinates[*].name
    }

七、图遍历关联 #

7.1 基本图遍历 #

aql

FOR v, e IN 1..1 OUTBOUND "users/user_001" follows
    RETURN {
        userId: v._key,
        userName: v.name,
        followedAt: e.createdAt
    }

7.2 多跳关联 #

aql

FOR v, e, p IN 1..2 OUTBOUND "users/user_001" follows
    RETURN {
        userId: v._key,
        userName: v.name,
        path: p.vertices[*].name,
        depth: LENGTH(p.edges)
    }

7.3 双向关联 #

aql

FOR v, e IN ANY "users/user_001" follows
    RETURN {
        userId: v._key,
        userName: v.name,
        direction: e._from == "users/user_001" ? "following" : "follower"
    }

八、实战示例 #

8.1 订单详情查询 #

aql

FOR order IN orders
    FILTER order._key == @orderId
    LET user = DOCUMENT("users", order.userId)
    LET items = (
        FOR item IN orderItems
            FILTER item.orderId == order._key
            LET product = DOCUMENT("products", item.productId)
            RETURN {
                productId: item.productId,
                productName: product.name,
                quantity: item.quantity,
                price: item.price,
                subtotal: item.quantity * item.price
            }
    )
    RETURN {
        orderId: order._key,
        status: order.status,
        createdAt: order.createdAt,
        user: {
            id: user._key,
            name: user.name,
            email: user.email
        },
        items: items,
        totalAmount: SUM(items[*].subtotal)
    }

8.2 用户中心数据 #

aql

FOR user IN users
    FILTER user._key == @userId
    LET orders = (
        FOR order IN orders
            FILTER order.userId == user._key
            SORT order.createdAt DESC
            LIMIT 5
            RETURN {
                orderId: order._key,
                amount: order.amount,
                status: order.status,
                createdAt: order.createdAt
            }
    )
    LET stats = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT AGGREGATE
                totalOrders = COUNT(),
                totalSpent = SUM(order.amount),
                avgOrderValue = AVG(order.amount)
            RETURN { totalOrders, totalSpent, avgOrderValue }
    )[0]
    LET favorites = (
        FOR fav IN favorites
            FILTER fav.userId == user._key
            LET product = DOCUMENT("products", fav.productId)
            RETURN product
    )
    RETURN {
        user: user,
        recentOrders: orders,
        stats: stats || { totalOrders: 0, totalSpent: 0, avgOrderValue: 0 },
        favorites: favorites
    }

8.3 商品详情页 #

aql

FOR product IN products
    FILTER product._key == @productId
    LET category = DOCUMENT("categories", product.categoryId)
    LET reviews = (
        FOR review IN reviews
            FILTER review.productId == product._key
            LET user = DOCUMENT("users", review.userId)
            RETURN {
                reviewId: review._key,
                rating: review.rating,
                content: review.content,
                userName: user.name,
                createdAt: review.createdAt
            }
    )
    LET relatedProducts = (
        FOR p IN products
            FILTER p.categoryId == product.categoryId
            AND p._key != product._key
            LIMIT 4
            RETURN {
                id: p._key,
                name: p.name,
                price: p.price,
                image: p.image
            }
    )
    LET avgRating = AVG(reviews[*].rating)
    RETURN {
        product: product,
        category: category.name,
        reviews: reviews,
        reviewCount: LENGTH(reviews),
        avgRating: avgRating || 0,
        relatedProducts: relatedProducts
    }

8.4 社交关系查询 #

aql

FOR user IN users
    FILTER user._key == @userId
    LET followers = (
        FOR v, e IN INBOUND user._id follows
            RETURN {
                id: v._key,
                name: v.name,
                avatar: v.avatar,
                followedAt: e.createdAt
            }
    )
    LET following = (
        FOR v, e IN OUTBOUND user._id follows
            RETURN {
                id: v._key,
                name: v.name,
                avatar: v.avatar,
                followedAt: e.createdAt
            }
    )
    LET mutualFollows = (
        FOR follower IN followers
            FOR f IN following
                FILTER follower.id == f.id
                RETURN follower
    )
    RETURN {
        user: user,
        followerCount: LENGTH(followers),
        followingCount: LENGTH(following),
        mutualCount: LENGTH(mutualFollows),
        followers: followers,
        following: following
    }

九、性能优化 #

9.1 使用索引 #

javascript

db.orders.ensureHashIndex(["userId"]);
db.orders.ensureHashIndex(["productId"]);

9.2 限制关联数量 #

aql

FOR user IN users
    LET orders = (
        FOR order IN orders
            FILTER order.userId == user._key
            LIMIT 10
            RETURN order
    )
    RETURN {
        user: user.name,
        orders: orders
    }

9.3 使用DOCUMENT代替子查询 #

aql

FOR order IN orders
    LET user = DOCUMENT("users", order.userId)
    RETURN {
        orderId: order._key,
        userName: user.name
    }

9.4 避免N+1查询 #

aql

LET userMap = MERGE(
    FOR user IN users
        RETURN { [user._key]: user }
)
FOR order IN orders
    LET user = userMap[order.userId]
    RETURN {
        orderId: order._key,
        userName: user.name
    }

十、总结 #

多集合查询要点：

嵌套FOR：最常用的关联方式
子查询：灵活处理复杂关联
DOCUMENT：快速获取单个文档
图遍历：基于边集合的关联
性能优化：使用索引、限制数量

下一步，让我们学习图数据库！