hasManyJson, multiple foreign keys about eloquent-json-relations

[staudenmeir]

Can you share your use case with some sample data?

Is it an OR constraint where the email address needs to be in email[]->to or email[]->from?

[Intrflex]

Can you share your use case with some sample data?

Is it an OR constraint where the email address needs to be in email[]->to or email[]->from?

I have a table contacts with an email column, I then have an inbound email table with 2 columns to & from both of which are JSON for example {"name": "intrflex", "email": "[email protected]" i need to be able to do a HasManyJson to the 2 email fields to & from where the email is either the recipient or the receiver

[Intrflex]

but essentially yes its an OR constraint new to this, unfortunately, its either going to be from the person or to the person

[staudenmeir]

The package itself doesn't support that, but you can use one of my other packages:
https://github.com/staudenmeir/laravel-merged-relations

Create two HasManyJson relationships (one for to and one for from) and combine them with mergedRelationWithModel().

[Intrflex]

I know this will work with the from column as that's only a JSON object, however, the to column is a JSON array? will this package still work

[Intrflex]

it returns the following error PDOException::("SQLSTATE[42S22]: Column not found: 1054 Unknown column 'inbound_emails.to[]' in 'field list'"

[staudenmeir]

I'll look into it.

[Intrflex]

Thankyou, really trying to get this to work

[staudenmeir]

What version of MySQL/MariaDB are you using?

[Intrflex]

10.4.13-MariaDB

[Intrflex]

   public function mailTo(): HasManyJson
    {
      return $this->hasManyJson(InboundEmail::class, 'to[]->email' , 'email')->where('public', 1);
    }

    public function mailFrom(): HasManyJson
    {
        return $this->hasManyJson(InboundEmail::class, 'from->email', 'email')->where('public', 1);
    }

thats both my relationships

and this is my schema
Schema::createMergeView('emailables', [(new Contact)->mailTo(), (new Contact)->mailFrom()]);

[staudenmeir]

You're right, JSON array don't work out of the box. I see two options:

• You extend the HasManyJson relationship in your project and add support for multiple foreign keys. Depending on your use case, this will take a while.

• You use the laravel-merged-relations package, but create the view manually (you need to add the rest of your columns):

$view = <<<EOT
CREATE VIEW `emailables` AS
WITH RECURSIVE `to_emails` AS
  (SELECT *,
          -1 AS `i`
   FROM `inbound_emails`
   UNION SELECT `inbound_emails`.*,
                `to_emails`.`i` + 1
   FROM `inbound_emails`
   JOIN `to_emails` ON `inbound_emails`.`id`=`to_emails`.`id`
   WHERE `to_emails`.`i` < JSON_LENGTH(`inbound_emails`.`to`) - 1 )
  (SELECT *, JSON_UNQUOTE(JSON_EXTRACT(`inbound_emails`.`from`, '$."email"')) AS `laravel_foreign_key`,
          'App\\\\Models\\\\InboundEmail' AS `laravel_model`,
          '' AS `laravel_placeholders`,
          '' AS `laravel_with`
   FROM `inbound_emails`
   WHERE `public` = 1 )
UNION
  (SELECT `id`,
          `to`,
          `from`,
          `public`,
          JSON_UNQUOTE(JSON_EXTRACT(`to`, CONCAT('$[', `i`, '].email'))) `laravel_foreign_id`,
          'App\\\\Models\\\\InboundEmail' AS `laravel_model`,
          '' AS `laravel_placeholders`,
          '' AS `laravel_with`
   FROM `to_emails`
   WHERE `public` = 1
     AND `i` >= 0 );
EOT;

DB::statement($view);

Then you can create the merged relationship:

class Contact extends Model
{
    use HasMergedRelationships;

    public function emailables(): MergedRelation
    {
        return $this->mergedRelationWithModel(InboundEmail::class, 'emailables', 'email');
    }
}

[Intrflex]

• id
• user_id
• public
• email_id
• message_id
• thread_id
• from
• to
• cc
• subject
• email_date
• message
• labels
• snippet
• deleted_at
• created_at
• updated_at

MySQL queries arent really my speciality, i dont even really understand where id add my other columns in ive included my database columns

[Intrflex]

• id
• user_id
• public
• email_id
• message_id
• thread_id
• from
• to
• cc
• subject
• email_date
• message
• labels
• snippet
• deleted_at
• created_at
• updated_at

MySQL queries arent really my speciality, i dont even really understand where id add my other columns in I've included my database columns

Okay scrap that message i worked it out but, Holy that query takes forever to run it doesn't even complete on a table of 20000 emails laravel returns a 504 time out

[Intrflex]

Just an update, I worked out a temporary fix, I've extended your MergedRelation swapped out the query so it searches for a foreign key like the email i'm trying to find, hopefully one day this package can support multiple foreign keys i found another packaged that merged yours with compoships but unfortunately the functionality i needed was released in the packages it used

[staudenmeir]

Can you share the code of your solution for others?