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 8 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...
insert into `field_rule_results` (`field_rule_id`, `typeable_id`, `typeable_type`, `content`, `updated_at`, `created_at`) values (..)
insert into `field_rule_results` (`field_rule_id`, `typeable_id`, `typeable_type`, `content`, `updated_at`, `created_at`) values (..)
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?
updateOrCreate
method will trigger aselect
query first in order to figure out whether it should do aninsert
or anupdate
query. another way could be doing aninsert
with ON DUPLICATE KEY UPDATE. \$\endgroup\$