Repository: statamic/eloquent-driver (php)

Issue:
The offset parameter does not work on collections
When I try this:
```
{{ collection:articles offset="1" }}
  {{ partial src="partials/article_teaser" }}
{{ /collection:articles }}
```
it throws an error, and I think the problem is the way the SQL query is built:
```
select * from `entries` where `collection` in (articles) and `status` = published and `date` < 2023-06-21 23:35:12 and (json_extract(`data`, '$."redirect"') is null OR json_type(json_extract(`data`, '$."redirect"')) = 'NULL') order by json_unquote(json_extract(`data`, '$."title"')) desc offset 1
```
The error is:
**SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'offset 1' at line 1**

Here is the setup:
Environment
Application Name: xxx
Laravel Version: 9.52.7
PHP Version: 8.1.17
Composer Version: 2.5.5
Environment: local
Debug Mode: ENABLED
URL: xxx.test
Maintenance Mode: OFF

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: statamic
Database: mysql
Logs: stack / single
Mail: smtp
Queue: sync
Session: file

Statamic
Addons: 6
Antlers: runtime
Stache Watcher: Enabled
Static Caching: Disabled
Version: 3.4.11 PRO

Statamic Addons
aryehraber/statamic-captcha: 1.10.0
properti/estate-items-import: dev-main
properti/properti-dashboard: dev-main
r8edge/imagemap: dev-main
r8edge/welcome-widget: dev-main
statamic/eloquent-driver: 1.2.0


Pull request:
Fix offset being specified without limit and without pagination on EntryQueryBuilder
This PR checks for the presence of offset without limit on entry queries, which throws an error on some database engines (looking at you MySQL). 

Laravel have chosen to not provide a work around in their query builder, but we should as the Stache allows this and we want to be compatible.

Closes #175 

Patch:
diff --git a/database/migrations/create_entries_table.php.stub b/database/migrations/create_entries_table.php.stub
index 52cb8a3..f216a4a 100644
--- a/database/migrations/create_entries_table.php.stub
+++ b/database/migrations/create_entries_table.php.stub
@@ -10,22 +10,17 @@ return new class extends Migration {
         Schema::create($this->prefix('entries'), function (Blueprint $table) {
             $table->id();
             $table->string('site')->index();
-            $table->unsignedBigInteger('origin_id')->nullable();
+            $table->unsignedBigInteger('origin_id')->nullable()->index();
             $table->boolean('published')->default(true);
             $table->string('status');
             $table->string('slug')->nullable();
             $table->string('uri')->nullable()->index();
             $table->string('date')->nullable();
-            $table->integer('order')->nullable();
+            $table->integer('order')->nullable()->index();
             $table->string('collection')->index();
             $table->string('blueprint', 30)->nullable()->index();
             $table->jsonb('data');
             $table->timestamps();
-
-            $table->foreign('origin_id')
-                ->references('id')
-                ->on($this->prefix('entries'))
-                ->onDelete('set null');
         });
     }
 
diff --git a/database/migrations/create_entries_table_with_string_ids.php.stub b/database/migrations/create_entries_table_with_string_ids.php.stub
index 27235f4..c738a43 100644
--- a/database/migrations/create_entries_table_with_string_ids.php.stub
+++ b/database/migrations/create_entries_table_with_string_ids.php.stub
@@ -10,23 +10,19 @@ return new class extends Migration {
         Schema::create($this->prefix('entries'), function (Blueprint $table) {
             $table->uuid('id');
             $table->string('site')->index();
-            $table->uuid('origin_id')->nullable();
+            $table->uuid('origin_id')->nullable()->index();
             $table->boolean('published')->default(true);
             $table->string('status');
             $table->string('slug')->nullable();
             $table->string('uri')->nullable()->index();
             $table->string('date')->nullable();
-            $table->integer('order')->nullable();
+            $table->integer('order')->nullable()->index();
             $table->string('collection')->index();
             $table->string('blueprint', 30)->nullable()->index();
             $table->jsonb('data');
             $table->timestamps();
 
             $table->primary('id');
-            $table->foreign('origin_id')
-                ->references('id')
-                ->on($this->prefix('entries'))
-                ->onDelete('set null');
         });
     }
 
diff --git a/database/migrations/create_form_submissions_table.php.stub b/database/migrations/create_form_submissions_table.php.stub
index 1487dcc..f0e763a 100644
--- a/database/migrations/create_form_submissions_table.php.stub
+++ b/database/migrations/create_form_submissions_table.php.stub
@@ -9,7 +9,7 @@ return new class extends Migration {
     {
         Schema::create($this->prefix('form_submissions'), function (Blueprint $table) {
             $table->id();
-            $table->foreignId('form_id')->constrained($this->prefix('forms'))->cascadeOnDelete();
+            $table->unsignedBigInteger('form_id')->index();
             $table->jsonb('data')->nullable();
             $table->timestamps(6);
 
diff --git a/database/migrations/updates/drop_foreign_keys_on_entries.php.stub b/database/migrations/updates/drop_foreign_keys_on_entries.php.stub
new file mode 100644
index 0000000..c3c5f0a
--- /dev/null
+++ b/database/migrations/updates/drop_foreign_keys_on_entries.php.stub
@@ -0,0 +1,26 @@
+<?php
+
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+use Statamic\Eloquent\Database\BaseMigration as Migration;
+
+return new class extends Migration {
+    public function up()
+    {
+        Schema::table($this->prefix('entries'), function (Blueprint $table) {
+            $table->dropForeign(['origin_id']);
+            $table->index('order');
+        });
+    }
+
+    public function down()
+    {
+        Schema::table($this->prefix('entries'), function (Blueprint $table) {
+            $table->foreign('origin_id')
+                ->references('id')
+                ->on($this->prefix('entries'))
+                ->onDelete('set null');
+            $table->dropIndex(['order']);
+        });
+    }
+};
diff --git a/database/migrations/updates/drop_foreign_keys_on_forms.php.stub b/database/migrations/updates/drop_foreign_keys_on_forms.php.stub
new file mode 100644
index 0000000..ae2591b
--- /dev/null
+++ b/database/migrations/updates/drop_foreign_keys_on_forms.php.stub
@@ -0,0 +1,24 @@
+<?php
+
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+use Statamic\Eloquent\Database\BaseMigration as Migration;
+
+return new class extends Migration {
+    public function up()
+    {
+        Schema::table($this->prefix('form_submissions'), function (Blueprint $table) {
+            $table->dropForeign(['origin_id']);
+        });
+    }
+
+    public function down()
+    {
+        Schema::table($this->prefix('form_submissions'), function (Blueprint $table) {
+            $table->foreign('form_id')
+                ->references('id')
+                ->on($this->prefix('forms'))
+                ->onDelete('cascade');
+        });
+    }
+};
diff --git a/src/Entries/EntryQueryBuilder.php b/src/Entries/EntryQueryBuilder.php
index bbabf30..29bfdd6 100644
--- a/src/Entries/EntryQueryBuilder.php
+++ b/src/Entries/EntryQueryBuilder.php
@@ -62,6 +62,11 @@ class EntryQueryBuilder extends EloquentQueryBuilder implements QueryBuilder
 
     public function get($columns = ['*'])
     {
+        $query = $this->builder->getQuery();
+        if ($query->offset && ! $query->limit) {
+            $query->limit = PHP_INT_MAX;
+        }
+
         $this->addTaxonomyWheres();
 
         return parent::get($columns);
diff --git a/src/Fields/BlueprintRepository.php b/src/Fields/BlueprintRepository.php
index 4800d83..3537805 100644
--- a/src/Fields/BlueprintRepository.php
+++ b/src/Fields/BlueprintRepository.php
@@ -114,6 +114,13 @@ class BlueprintRepository extends StacheRepository
         return [$namespace, $handle];
     }
 
+    public function getModel($blueprint)
+    {
+        return app('statamic.eloquent.blueprints.blueprint_model')::where('namespace', $blueprint->namespace() ?? null)
+            ->where('handle', $blueprint->handle())
+            ->first();
+    }
+
     public function updateModel($blueprint)
     {
         $model = app('statamic.eloquent.blueprints.blueprint_model')::firstOrNew([
@@ -121,15 +128,20 @@ class BlueprintRepository extends StacheRepository
             'namespace' => $blueprint->namespace() ?? null,
         ]);
 
-        $model->data = $this->addOrderToBlueprintSections($blueprint->contents());
+        $data = $this->addOrderToBlueprintSections($blueprint->contents());
+
+        if (! $blueprint->hidden()) {
+            unset($data['hide']);
+        }
+
+        $model->data = $data;
+
         $model->save();
     }
 
     public function deleteModel($blueprint)
     {
-        $model = app('statamic.eloquent.blueprints.blueprint_model')::where('namespace', $blueprint->namespace() ?? null)
-            ->where('handle', $blueprint->handle())
-            ->first();
+        $model = $this->getModel($blueprint);
 
         if ($model) {
             $model->delete();
diff --git a/src/ServiceProvider.php b/src/ServiceProvider.php
index eb5c9ea..4709bfc 100644
--- a/src/ServiceProvider.php
+++ b/src/ServiceProvider.php
@@ -42,6 +42,7 @@ class ServiceProvider extends AddonServiceProvider
     protected $updateScripts = [
         \Statamic\Eloquent\Updates\AddOrderToEntriesTable::class,
         \Statamic\Eloquent\Updates\AddBlueprintToEntriesTable::class,
+        \Statamic\Eloquent\Updates\DropForeignKeysOnEntriesAndForms::class,
     ];
 
     protected $listen = [
diff --git a/src/Updates/DropForeignKeysOnEntriesAndForms.php b/src/Updates/DropForeignKeysOnEntriesAndForms.php
new file mode 100644
index 0000000..b4a3b48
--- /dev/null
+++ b/src/Updates/DropForeignKeysOnEntriesAndForms.php
@@ -0,0 +1,29 @@
+<?php
+
+namespace Statamic\Eloquent\Updates;
+
+use Statamic\UpdateScripts\UpdateScript;
+
+class DropForeignKeysOnEntriesAndForms extends UpdateScript
+{
+    public function shouldUpdate($newVersion, $oldVersion)
+    {
+        return $this->isUpdatingTo('2.3.0');
+    }
+
+    public function update()
+    {
+        $source = __DIR__.'/../../database/migrations/updates/drop_foreign_keys_on_entries.php.stub';
+        $dest = database_path('migrations/'.date('Y_m_d_His').'_drop_foreign_keys_on_entries.php');
+
+        $this->files->copy($source, $dest);
+
+        $source = __DIR__.'/../../database/migrations/updates/drop_foreign_keys_on_forms.php.stub';
+        $dest = database_path('migrations/'.date('Y_m_d_His').'_drop_foreign_keys_on_forms.php');
+
+        $this->files->copy($source, $dest);
+
+        $this->console()->info('Migrations created');
+        $this->console()->comment('Remember to run `php artisan migrate` to apply it to your database.');
+    }
+}


Test patch:
diff --git a/tests/Data/Entries/EntryQueryBuilderTest.php b/tests/Data/Entries/EntryQueryBuilderTest.php
index c8ab112..fd03e62 100644
--- a/tests/Data/Entries/EntryQueryBuilderTest.php
+++ b/tests/Data/Entries/EntryQueryBuilderTest.php
@@ -689,6 +689,21 @@ class EntryQueryBuilderTest extends TestCase
         $this->assertEquals(['Post 2', 'Post 3'], $entries->map->title->all());
     }
 
+    /** @test */
+    public function entries_are_found_using_offset_but_no_limit()
+    {
+        $this->createDummyCollectionAndEntries();
+
+        $entries = Entry::query()->get();
+        $this->assertCount(3, $entries);
+        $this->assertEquals(['Post 1', 'Post 2', 'Post 3'], $entries->map->title->all());
+
+        $entries = Entry::query()->offset(1)->get();
+
+        $this->assertCount(2, $entries);
+        $this->assertEquals(['Post 2', 'Post 3'], $entries->map->title->all());
+    }
+
     /** @test */
     public function entries_can_be_retrieved_on_join_table_conditions()
     {
diff --git a/tests/Data/Fields/BlueprintTest.php b/tests/Data/Fields/BlueprintTest.php
new file mode 100644
index 0000000..44953f6
--- /dev/null
+++ b/tests/Data/Fields/BlueprintTest.php
@@ -0,0 +1,73 @@
+<?php
+
+namespace Tests\Data\Fields;
+
+use Illuminate\Foundation\Testing\RefreshDatabase;
+use Statamic\Facades\Blueprint;
+use Tests\TestCase;
+
+class BlueprintTest extends TestCase
+{
+    use RefreshDatabase;
+
+    public function setUp(): void
+    {
+        parent::setUp();
+
+        $this->app->singleton(
+            'Statamic\Fields\BlueprintRepository',
+            'Statamic\Eloquent\Fields\BlueprintRepository'
+        );
+
+        $this->app->singleton(
+            'Statamic\Fields\FieldsetRepository',
+            'Statamic\Eloquent\Fields\FieldsetRepository'
+        );
+
+        $this->app->bind('statamic.eloquent.blueprints.blueprint_model', function () {
+            return \Statamic\Eloquent\Fields\BlueprintModel::class;
+        });
+
+        $this->app->bind('statamic.eloquent.blueprints.fieldset_model', function () {
+            return \Statamic\Eloquent\Fields\FieldsetModel::class;
+        });
+    }
+
+    /** @test */
+    public function it_saves_and_removes_hidden_on_model()
+    {
+        $blueprint = Blueprint::make()
+            ->setHandle('test')
+            ->setHidden(true)
+            ->save();
+
+        $model = Blueprint::getModel($blueprint);
+
+        $this->assertTrue($model->data['hide']);
+
+        $blueprint->setHidden(false)->save();
+
+        $model = Blueprint::getModel($blueprint);
+
+        $this->assertFalse(isset($model->data['hide']));
+    }
+
+    /** @test */
+    public function it_deletes_the_model_when_the_blueprint_is_deleted()
+    {
+        $blueprint = Blueprint::make()
+            ->setHandle('test')
+            ->setHidden(true)
+            ->save();
+
+        $model = Blueprint::getModel($blueprint);
+
+        $this->assertNotNull($model);
+
+        $blueprint->delete();
+
+        $model = Blueprint::getModel($blueprint);
+
+        $this->assertNull($model);
+    }
+}