Thread

Hi,

When I look in my database after relating an article to another article, the custom field contains a value I cannot make sense of.

When I try to relate article 30 to for instance 60, I guessed that the custom field for record 30 that stores the relation, get’s the value 60. Somehow, this doens’t seem true. Can someone please exmplain the logic behind this all?

Thanks

Okay, just figured out it points to the table exp_relationships. Are the rel_data / reverse_rel_data required fields for that table? In other words, is there any way to create the relationship without populating these fields?

EE uses those fields to cache the relationship data for the weblog:entries {related_entries} and {reverse_related_entries} tags.  haven’t looked at the code to tell what exactly is stored, but it’s a serialized php array of some sort.  If you don’t generate that data I don’t think those tags will work… however if you’re just going to use custom queries to join to the related entries anyway I’d say go for it.

But I’m doing this to be able to rely on {related_entries} in my templates (instead of the query’s it’s using now). Anyone any insight of the content of the rel_data fields? It looks like JSON to me.

it’s a serialized() PHP array.  take one, run it through unserialize(), then print_r the contents.  that’ll give you an idea of what it contains.

I think EE should be able to regenerate these on its own, however.

Just to make sure you’re setting up the relationship correctly, this is what goes where:

0) if you don’t have a weblog entry yet, you’ll need to create that first… not in the scope of this thread
1) create a new row in exp_relationships:
- rel_parent_id is the entry_id of the entry with the relationship custom field
- rel_child_id is the entry_id of the entry being targeted by the relationship custom field
- rel_type should be ‘blog’ when relating weblog entries
2) edit the parent entry’s exp_weblog_data row…
- field_id_XX pertaining to your relationship custom field needs to be set with the rel_id of the row we just made in exp_relationships

I believe that is the minimum that needs to be done to set up a relationship that will work with the weblog:entries tags.

Okay, so basically the rel_data is not relevant for a working relation between two entries?

What I need to do is

- Select all entries by users from a certain usergroup (for instance 100 entries)
- Foreach of these entries I need to create a relationship to the same entry (this means 100 new relations)
- Store the relationship_id for each entry in the custom field

Is this doable with SQL? Can this be done in 1 query? Or should I forget about it?

I’d try manually entering one of those relationships by adding the correct data via phpmyadmin, navicat, or other DB management tool and seeing if it works.  after you’ve gotten that to work then worry about automating it.

I almost got it to work.. I managed to automatically create a relationship record using the following query:

INSERT INTO exp_relationships (rel_parent_id, rel_child_id)
SELECT parent.entry_id as rel_parent_id, child.entry_id as rel_child_id
FROM  `exp_weblog_data` as parent,
exp_weblog_data as child
WHERE parent.entry_id
IN (
    
    SELECT entry_id
    FROM  `exp_weblog_titles` 
    WHERE author_id
    IN (
        
        SELECT member_id
        FROM  `exp_members` 
        WHERE group_id = 23
    )
) 
AND child.entry_id = 5050
AND parent.weblog_id = 16

Where the variables are group_id and the second parameter of the INSERT INTO statement. This creates all the relationship records for a certain usergroup and a certain weblog.

Now what I need to do is for each of the articles found by these query (for which already relationships have been made), is save the rel_id in the custom field. I’m kind of troubled by finding the solution for this. I do not know how to do a multiple update :(

Any thoughts?

probably easiest to create a PHP script for this. you can use the $DB class through EE to run the query and you’ll be able to capture the rel_id by using $DB->insert_id.

edit: actually hold that thought… I think I might be able to come up with a query that’ll do this.

Hi Ender,

Thanks, but already written a (very simple actually) SQL query that can update all the correct records:

UPDATE exp_weblog_data, exp_relationships
SET exp_weblog_data.field_id_46 = exp_relationships.rel_id
WHERE 
exp_relationships.rel_parent_id = exp_weblog_data.entry_id
AND exp_weblog_data.weblog_id = 16
AND exp_relationships.rel_child_id = 5050

Some background, field_id_46 is my custom field for the required relationship. The second and third WHERE statements make sure only records in scope are affected. The rel_child_id corresponds with the rel_child_id previously saved in the query above.

If anyone has the same problem and reads this post, it is easily adapted to your personal situation, if you require any assistance, just post a reply.

Thanks!

yup, that looks like what I just came up with as well, without the hardcoded IDs specific to your situation.  glad it worked out.

Okay… so I finally finished making the relations for about 4.000 entries, now it seems the {reverse_related_entries} tag isn’t working… probably due to the empty rel_data / reverse_rel_data fields.

I’m basically a bit surprised that this even matters in a (simple) 1-to-many relationship, the data could be fetched on the fly easily.

Any thoughts on how to make this work?