search

statamic / eloquent-driver

Provides support for storing your Statamic data in a database, rather than flat files.
https://statamic.dev/tips/storing-content-in-a-database
MIT License
108 stars 78 forks source link

Optimize taxonomy <> collection queries #376

Closed TheBnl closed 1 week ago

TheBnl commented 2 weeks ago

Working on a draft to fix issue #373

The current implementation does not yet pass the tests due to not being fully db driver independent. The test fails because JSON_QUOTE seems to behave different in sqllite than mysql. The JSON_QUOTE should probably not be hard coded here as well because we can't assume the db driver that is in use.

Closes https://github.com/statamic/eloquent-driver/issues/373

ryanmitchell commented 2 weeks ago

@TheBnl let's move the conversation over here. I've modified the approach and have tests working on SQLite and it works fine on MySQL from my own testing.

Do you have access to sqlserver or postgres to check this on? It also needs some test coverage if you could work on that.

TheBnl commented 2 weeks ago

@TheBnl let's move the conversation over here. I've modified the approach and have tests working on SQLite and it works fine on MySQL from my own testing.

Do you have access to sqlserver or postgres to check this on? It also needs some test coverage if you could work on that.

Awesome! Will test on postgress, haven't came by to installing sqlserver yet. Will check and look into adding postgress/sqlserver to the github test workflow. :+1:

TheBnl commented 1 week ago

Tested with postgres and there we do need wrap it so the value will be handled as json: to_jsonb({$wrappedColumn}::text)

Working on the test coverage in a different branch, also to test sqlserver, as it is quite a bit of work of settings things up right.

ryanmitchell commented 1 week ago

Sorry I didn't mean we need to set up different testing environments for each database engine - I just meant we needed a test in the suite to cover this new behaviour.

TheBnl commented 1 week ago

Ah haha well that makes things quite simpler. I've added a test where the entries driver is set to 'file' so both the join and map gets tested.

ryanmitchell commented 1 week ago

Thanks for your work on this - really appreciate it.

TheBnl commented 1 week ago

Awesome thanks for merging!