sort by related column in laravel lighthouse graphql

Question

how can I sort by related column in laravel lighthouse graphql?

For example, I have Products that have One Category but I can't SortBy Category. How can I do that?

GraphQL

getProducts(orderBy: _ @orderBy): [Product!]! @all

type Product {
    id: ID
    code: String
    article: String

    category: Category
}
type Category {
    id: ID
    name: String
    products: [Product]
}

My Models where you can see the relationship between Products and Categories

class Product extends Model
{
    ...
    public function category()
    {
        return $this->belongsTo(Category::class);
    }
}


class Category extends Model
{
    ...
    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

SOLUTION

Create a Builder

<?php

namespace App\GraphQL\Queries;

use Illuminate\Database\Eloquent\Builder;

class ProductBuilder
{
    public function getProducts(Builder $builder)
    {
        return
            $builder->join('categories', 'categories.id', '=', 'products.category_id');
    }
}

Then add the builder to schema.graphql

getProducts(orderBy: _ @orderBy): [Product!]!
    @all
    @builder(method: "App\\GraphQL\\Queries\\ProductBuilder@getProducts")

Now you can call and order by relationship

{
    getProducts(orderBy: {column: "categories.name", order: ASC}){
        id
        code
        article
        category {
            name
       }
   }
}

Raimo Haikari · Answer 1 · 2022-10-09T18:59:17.793

3

If:

Product belongs to one Category
Category has many Products

Then Category class should look something like this:

class Category extends Model
{
   ....

   public function products(): HasMany
   {
    return $this->hasMany(Product::class, <id_field_in_category_table>, <category_field_name_in_products_table>);
   }

}

And Product class should resemble this:

class Product extends Model
{

    ...

    public function category(): BelongsTo
    {
        return $this->belongsTo(Category::class, <category_field_name_in_products_table>, <id_field_in_category_table> );
    }

}

2 )Then add following to your schema.graphql.php


input QueryGetProductsOrderByOrderByClause {
    "The column that is used for ordering."
    column: QueryGetProductsOrderByColumn!

    "The direction that is used for ordering."
    order: SortOrder!
}

"Allowed column names for Query.posts.orderBy"
enum QueryGetProductsOrderByColumn {
    CATEGORY @enum(value: "category.name")
}

"Directions for ordering a list of records."
enum SortOrder {
    "Sort records in ascending order."
    ASC

    "Sort records in descending order."
    DESC
}

Update Product and Category:


type Product {
    id: ID
    code: String
    article: String

    category: Category! @belongsTo
}

type Category {
    id: ID
    name: String
    products: [Product] @hasMany
}

Update query


Query {
    getProducts(orderBy: _ @orderBy(columns: ["category"])): [Product!]! @all
}

There was an error in my first post, it should be: @orderBy(columns: ["category"]).

Then you should be able to query:



{
    getProducts(orderBy: [{ column: CATEGORY, order: ASC }]){
        id
        code
        article
        category {
            name
       }
   }
}

edited Oct 09 '22 at 18:59

answered Oct 06 '22 at 13:21

Raimo Haikari

121
1
3

I tried your approach and have the next error. "Field \"getProducts\" argument \"orderBy\" requires type QueryGetProductsOrderByColumn, found CATEGORY." And if I try Query { getProducts( orderBy: _ @orderBy(columnsEnum: "QueryGetProductsOrderByColumn") ): [Product!]! @all } enum QueryGetProductsOrderByColumn { CATEGORY @enum(value: "category.name") } Error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category.name' in 'order clause' (SQL: select * from `products` where `products`.`deleted_at` is null order by `category`.`name` asc) – Agustin Oct 09 '22 at 16:39
@Agustin I had an error in column name in the section: "4. Update query". Please check if fixing column name helps. – Raimo Haikari Oct 09 '22 at 19:03
It's like the relation is not make when the query is executed /ERROR MESSAGE: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category' in 'order clause' (SQL: select * from `products` where `products`.`deleted_at` is null order by `category` asc) – Agustin Oct 09 '22 at 19:09
1

@Agustin And after closer look it seem's that just putting CATEGORY @enum(value: "category.name") doesn't do the trick:( I'll try to find another solution because I need to implement this behavior in my own project too... – Raimo Haikari Oct 09 '22 at 20:04
Yes, stay in contact to see how we can resolve this. Thanks for your time, points 4u – Agustin Oct 09 '22 at 21:25
See my solution, let me know what you think – Agustin Oct 09 '22 at 21:50
1

@Agustin I think your solution is the right way to do it. Works like a charm in my project too, where I'm working with movies and genres, actors and so on. Just spent a couple hours trying experimenting with a model accessors, but it didn't work either. I don't feel comfortable with the Eloquent model and try to avoid using it as long as possible :) – Raimo Haikari Oct 10 '22 at 14:04

score 1 · Answer 2 · answered Oct 05 '22 at 08:00

1

I think the query variable part is not correct. Check this:

{"orderBy":
    [
        {"category": 
            {"aggregate": "MAX", "column": "name"}, 
            "order": "ASC"
        },
        {"category": 
            {"aggregate": "COUNT"}, 
            "order": "ASC"
        }
    ]
}

answered Oct 05 '22 at 08:00

mostafa

196
4

But that structure uses aggregate (numbers) to count or take the max from the collection of items from the relationship. What I need is to show and sort by (text) the only category that the product has in its relationship – Agustin Oct 05 '22 at 18:59
1

What you are talking is need to be implemented by a custom builder. For such sorting one way would be to make a join between products and the category_name. This join needs to be implemented in a custom builder. The lighthouse orderBy is using laravel feature which counts without loading the model(https://laravel.com/docs/9.x/eloquent-relationships#aggregating-related-models) and this type of orderBy that you need is not possible. – mostafa Oct 06 '22 at 08:06
Thanks, @mostafa, I think so, I will let the question open, I think that this is something that more than one asks himself, maybe someone has a nice approach – Agustin Oct 09 '22 at 15:03

sort by related column in laravel lighthouse graphql

SOLUTION

2 Answers2