#Improve Request to Reduce Queries

I have a web application, where users can upload Documents or Emails, to what I call a Strema. The users can then define document fields email fields to the stream, that each document/email will inherit. The users can then furthermore apply parsing rules to these fields, that each document/email will be parsed after.

Now let's take the example, that an user uploads a new document. (I have hardcoded the ID's for simplicty).

$stream = Stream::find(1);
$document = Document::find(2);

$parsing = new ApplyParsingRules;
$document->storeContent($parsing->parse($stream, $document));

Below is the function that parses the document according to the parsing rules:

    public function parse(Stream $stream, DataTypeInterface $data) : array
    {
        //Get the rules.
        $rules = $data->rules();
        
        $result = [];
        foreach ($rules as $rule) {

            $result[] = [
                'field_rule_id' => $rule->id,
                'content' => 'something something',
                'typeable_id' => $data->id,
            ];
        }

        return $result;
    }

So above basically just returns an array of the parsed text.

Now as you probably can see, I use an interface $DataTypeInterface. This is because the parse function can accept both Documents and Emails.

To get the rules, I use this code:

//Get the rules.
$rules = $data->rules();

The method looks like this:

class Document extends Model implements DataTypeInterface
{
    public function stream()
    {
        return $this->belongsTo(Stream::class);
    }
    public function rules() : object
    {
        return FieldRule::where([
            ['stream_id', '=', $this->stream->id],
            ['fieldable_type', '=', 'App\DocumentField'],
        ])->get();
    }
}

This will query the database, for all the rules that is associated with Document Fields and the fields, that is associated with the specific Stream.

Last, in my first request, I had this:

$document->storeContent($parsing->parse($stream, $document));

The storeContent method looks like this:

class Document extends Model implements DataTypeInterface
{
    // A document will have many field rule results.
    public function results()
    {
        return $this->morphMany(FieldRuleResult::class, 'typeable');
    }
    // Persist the parsed content to the database.
    public function storeContent(array $parsed) : object
    {
        foreach ($parsed as $parse) {
            $this->results()->updateOrCreate(
                [
                    'field_rule_id' => $parse['field_rule_id'],
                    'typeable_id' => $parse['typeable_id'],
                ],
                $parse
            );
        }
        return $this;
    }
}

As you can probably imagine, everytime a document gets parsed, it will create be parsed by some specific rules. These rules will all generate a result, thus I am saving each result in the database, using the storeContent method.

However, this will also generate a query for each result.

One thing to note: I am using the updateOrCreate method to store the field results, because I only want to persist new results to the database. All results where the content was just updated, I want to update the existing row in the database.

For reference, above request generates below queries:

select * from `streams` where `streams`.`id` = ? limit 1
select * from `documents` where `documents`.`id` = ? limit 1
select * from `streams` where `streams`.`id` = ? limit 1    
select * from `field_rules` where (`stream_id` = ? and `fieldable_type` = ?)
select * from `field_rule_results` where `field_rule_results`.`typeable_id` = ? and...
select * from `field_rule_results` where `field_rule_results`.`typeable_id` = ? and...  
select * from `users` where `id` = ? limit 1

Above works fine - but seems a bit heavy, and I can imagine once my users starts to generate a lot of rules/results, this will be a problem.

Is there any way that I can optimize/refactor above setup?