Filters in sort

[webard]

Hi,
look at this query:

query {
  categories(
    first: 100
    sort: [
      {
        translation: {
          name: DESC
        }
      }
    ]
    filter: {
      translation: {
        where: {
          language_id: {
            equal: "en-GB"
          }
        }
      }
    }
  ) {
    data {
      id
      translation(
        language_id: "en-GB"
      ) {
        name
      }
    }
  }
}

As you see, I filter product that have language "en-GB" in translations, and sort them by language.name. But my first language in table is "pl", so results are sorted by first language in table.

One of proposal is extend @sortby directive to accept simple "where" condition:

query {
  categories(
    first: 100
    sort: [
      {
        translation: {
          where: {
          language_id: {
            equal: "en-GB"
          }
        }
          name: DESC
        }
      }
    ]
    filter: {
      translation: {
        where: {
          language_id: {
            equal: "en-GB"
          }
        }
      }
    }
  ) {
    data {
      id
      translation(
        language_id: "en-GB"
      ) {
        name
      }
    }
  }
}

or make search in @searchby directive:

query {
  categories(
    first: 100
    sort: [
      {
        translation: {
          name: DESC
        }
      }
    ]
    filter: {
      translation: {
        where: {
          language_id: {
            equal: "en-GB"
            sort: true
          }
        }
      }
    }
  ) {
    data {
      id
      translation(
        language_id: "en-GB"
      ) {
        name
      }
    }
  }
}

What do you think?

[LastDragon-ru]

Seems each category has multiple translations? This case, unfortunately, is not yet supported. But I'm not sure that fully understand what are you trying to achieve... So maybe you can provide minimal db structure, graphql schema, and expected results?

[webard]

Yes, each category has multiple translations. First query from post above generates this SQL query:

select
  *
from
  `categories`
where
  (
    exists (
      select
        *
      from
        `category_translations`
      where
        `categories`.`id` = `category_translations`.`category_id`
        and `category_translations`.`language_id` = 'en-GB'
    )
  )
order by
  (
    select
      `category_translations`.`name`
    from
      `category_translations`
    where
      `categories`.`id` = `category_translations`.`category_id`
    limit
      1
  ) desc
limit
  100 offset 0

As you can see, in WHERE clause are conditions to fetch only en-gb translations, but in ORDER clause there is only joining translations by id, default ordered by primary key. So, if language "en-gb" is not created firstly, there is sorting by first matched row (eg. pl language, or any other).

[LastDragon-ru]

In the master branch (and the next major version) you can try to add Condition operator (or better a subclass because it will allow to redefine extra field name) into extra operators for @sortBy (same as for Random) and technically it may work 😀

[webard]

Hello,

so, if I understand correctly, in Condition operator I should detect that in @searchby is used "translations" table/filter, get the language_id value and provide it to @sortby condition?

Ps. you do awesome job, thank you!

[LastDragon-ru]

Nope, I meant something like

    'sort_by'   => [
        /**
         * Operators
         * ---------------------------------------------------------------------
         *
         * You can (re)define types and supported operators here.
         *
         * @see Operator
         */
        'operators' => [
            SortByOperators::Extra => [
                \LastDragon_ru\LaraASP\GraphQL\Builder\Contracts\Operator\Condition::class,
            ],
        ],
    ],

but, as I realized now, it unfortunately will not work :( So you need to create a custom operator that will create a custom type, you can check Relation as example.

[LastDragon-ru]

It is possible to achieve by custom types/operators, but I'm not sure the package should support it out of the box. Sorry. So I will close it for now.