-1

I'm trying to migrate a Laravel app with spatie/laravel-translatable 6.3 from MySQL 8 into Postgres 14: in a table defined

Schema::create('quiz_categories', function (Blueprint $table) {
        $table->id();
        $table->json('name')->comment('This column is used for i18n support');
        $table->boolean('active')->default(false);

and in app/Models/QuizCategory.php:

class QuizCategory extends Model
{
    use HasTranslations;

    protected $table = 'quiz_categories';
    public $translatable = ['name'];
    protected $primaryKey = 'id';
    public $timestamps = false;
    protected $casts = [
        'active' => 'boolean',
        'name' => 'json'
    ];
    protected $fillable = ['name', 'active'];

    public function scopeGetByActive($query, $active = null)
    {
        if (! isset($active)) {
            return $query;
        }

        return $query->where($this->table . '.active', (bool)$active);
    }

But with request

$quizCategories = QuizCategory::orderBy('name', 'desc')
    ->getByActive($filterActive)->get();

I got error:

SQLSTATE[42883]: Undefined function: 7 ERROR: could not identify an ordering operator for type json LINE 1: ...s" where "quiz_categories"."active" = $1 order by "name" des... ^ HINT: Use an explicit ordering operator or modify the query.

SELECT * FROM "quiz_categories" WHERE "quiz_categories"."active" = 1 ORDER BY "name" DESC

Testing the SQL query in PhpgMyAdmin looks like I need :

a) to remake request to be rendered as

 "quiz_categories"."active" = true

Not sure how can I do it in eloquent?

b) I need to remove

ORDER BY "name" DESC

Can I make ordering by JSON field somehow?

MySQL executed my code, but I am not sure that it worked correctly

UPDATED_INFO : I returned to this issue and looks like I skipped something last time, as with code:

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderByRaw('name->"$.en"', 'desc')
    ->get();

I got error :

SQLSTATE[42703]: Undefined column: 7 ERROR: column "$.en" does not exist LINE 1: select * from "quiz_categories" order by name->"$.en" ^
SELECT * FROM "quiz_categories" ORDER BY name->"$.en"

What is wrong ? Please give a reference to docs where such code is used...

UPDATED_INFO # 2 : With request :

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("json_extract('name', '$.en')"), 'desc')
  ->get();

I got error :

 function json_extract(unknown, unknown) does not exist

Searching in next I found method json_extract_path, but with code :

 $quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("json_extract_path('name', '$.en')"), 'desc')
  ->get();

I got different error :

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type json LINE 1: ...from "quiz_categories" order by json_extract_path('name', '$... ^ DETAIL: Token "name" is invalid. CONTEXT: JSON data, line 1: name

?

UPDATED_INFO # 3 : I use postgresql-14. I check https://www.postgresql.org/docs/14/functions-json.html

Looks like it has json_extract_path function (but I got error with it I mentioned above)

I found text :

jsonb -> text → jsonb
Extracts JSON object field with the given key.
'{"a": {"b":"foo"}}'::json -> 'a' → {"b":"foo"}

it looks like code with

'name->"$.en"'

But I have an error with it...

UPDATED_INFO # 4 :

With

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("name->>en"), 'desc')
    ->get();

I got error :

Undefined column: 7 ERROR: column "en" does not exist LINE 1: select * from "quiz_categories" order by name->>en desc ^
Improve this question
5
  • 1
    What exactly do you want to order by? If it's a JSON field, sorting by its value makes no sense. Maybe you want to sort by a "name" subfield or something? Commented Oct 17, 2023 at 8:57
  • a) How in eloquent remake to have sql generated "quiz_categories"."active" = true(NOT 1) b) If postgres alows someway of "name" subfield - how can I do it ? Commented Oct 17, 2023 at 11:10
  • What version of PHP are you using? Commented Oct 17, 2023 at 11:18
  • I have php 8.1. Does it depends on php version ? Commented Oct 17, 2023 at 11:31
  • There was a bug related to your first question on PHP 8.0.4, but your version would be fine. Could you please tag the question "php"? Commented Oct 17, 2023 at 11:32

1 Answer 1

Reset to default
0

Well there's no order by direct on json field because it's not a primitive type what you can in this case, you have to order by using raw sql.

I'm using order by english use whatever you want from that json column


QuizCategory::query()
    ->getByActive()
    ->orderByRaw('name->"$.en"', 'desc')
    ->get();

TIP: No need to specify table in scope function, a short version

public function scopeGetByActive($query, $active = null)
{
  $query->when($active !== null, fn($q) => $q->where('active',$active);
}

UPDATE:

QuizCategory::query()
    ->getByActive()
    orderBy(DB::raw("JSON_EXTRACT('name', '$.en')"), 'desc')
    ->get();

UPDATE 2: Please accept my apologies, I was giving you mySQL functions instead of PostgreSQL!

-> as JSON (if you have nested object you can use for ex: name->options->>'local')

->> to get the value as text

QuizCategory::query()
    ->getByActive()
    orderBy(DB::raw("name->>'en'"), 'desc')
    ->get();

More details on JSON in PostgreSQL

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Please read UPDATED_INFO :
Please read UPDATED_INFO # 2:
My apologies, here is the Postgres Version
don't use extract_...path function , use only "name->>en" as I mentioned in the 2nd Update
Let us continue this discussion in chat.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.