Bidirectional relationship between resources

15 Posts

rwam_de Reply #1, 12 years, 11 months ago

Hi @all,

I want to use a bidirectional relationship between different resources.

Current implementation:

There are a template "product" and a template "variant" (which id is 10). The template "product" has a template variable "variants". It's a Multiselect-Listbox with following input options:

@SELECT pagetitle, id FROM modx_site_content WHERE template = 10

On a product I can select one or more resources of type "variants". And on a chunk I create links with:

[[getResources?
      &parents=`-1`
      &resources=`[[+tv.variants:replace=`||==,`]]`
      &tpl=`customLinkTmpl`
]]

It works fine, the user can link to the variants. Yeah wink

My problem:

I want to link back from a variant to all linked products. How can I solve this or how can I get a list with all linked resources?

Attempt 1: Use of getRelated on template "variant": [[getRelated? &fields=`tv.variants:1,id:2`]] No success ;(
Attempt 2: Try to use the following snippet (see https://forums.modx.com/thread/37762/revolution-how-to-get-resources-with-specified-tv-type-date#dis-post-213651):

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->innerJoin('modTemplateVar','TemplateVar','TemplateVar.id = TemplateVarResources.tmplvarid AND TemplateVar.name = `variants`');
$c->where(array(
    'deleted' => false,
    'hidemenu' => false,
    'published' => true,
    'TemplateVarResources.value:' => $modx->resource->get('id')
));
$c->sortby('TemplateVarResources.value','ASC');
$resources = $modx->getCollection('modResource',$c);

No success ;(

Hm...

Any suggestions and tips would be great.

Thanks and ciao
Ralf

5,888 Posts

Bruno17 Reply #2, 12 years, 11 months ago

I think the Best Option is to have real two-way relations between resources.

something what the migxResourceRelations-example does:
https://github.com/Bruno17/migxResourceRelations

with a connection-table, something like here:
https://github.com/Bruno17/migxResourceRelations/blob/master/core/components/migxresourcerelations/model/schema/migxresourcerelations.mysql.schema.xml

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

15 Posts

rwam_de Reply #3, 12 years, 11 months ago

Hi Bruno,

thanks, that looks interesting, but it doesn't work (find attached the console output). May isn't there a much simpler solution like to create a sql-query? The query would be look like this (in my words):

Get all resources which have a TV 'variants' and where the value of this TV consists the current resource id

Ciao
Ralf

5,888 Posts

Bruno17 Reply #4, 12 years, 11 months ago

something like that (out of my head) should give you the ids of Related Resources.

$id = $modx->getOption('id',$scriptProperties,$modx->resource->get('id'));
$tv_id = $modx->getOption('tv_id',$scriptProperties);

$c = $modx->newQuery('modTemplateVarResource');
$c->where('tmplvarid'=>$tv_id));
$c->where("CONCAT('||',value,'||') LIKE '%||" . $id . "||%'", xPDOQuery::SQL_AND);

$ids = array();
if ($modx->getCollection('modTemplateVarResource',$c)){
    foreach ($collection as $object){
        $ids[]=$object->get('contentid');
    
    }
}

return implode(',',$ids);

[[getRelatedIds? &tv_id=`yourTvId`]]

[ed. note: Bruno17 last edited this post 12 years, 11 months ago.]

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

15 Posts

rwam_de Reply #5, 12 years, 11 months ago

Hi Bruno,

thanks. I use your code at a beginning, create my own query and now I have following snippet:

<?php
$id = $modx->getOption('id', $scriptProperties, $modx->resource->get('id'));
$tv_id = $modx->getOption('tv_id', $scriptProperties, 9);

$query = $modx->newQuery('modResource');
$query->innerJoin('modTemplateVarResource', 'TemplateVarResources');
$query->where(array(
    array(
        'TemplateVarResources.tmplvarid=' . $tv_id,
        'CONCAT(\'||\', TemplateVarResources.value, \'||\') LIKE \'%||' . $id . '||%\''
    )
));

$ids = array();
$collection = $modx->getCollection('modResource', $query);

foreach ($collection as $object) {
    $ids[] = $object->get('id');
}

return implode(',', $ids);

Without the innerJoin there are errors like the following:

(ERROR @ /myproject/index.php) Instantiated a derived class modTemplateVarResource that is not a subclass of the requested class modResource

And now it works without errors on the log file. I get all related resources wink

Many thanks and have a nice weekend
Ralf

5,888 Posts

Bruno17 Reply #6, 12 years, 11 months ago

My mistake was:

if ($modx->getCollection('modResource',$c))

changed it in the code above to

if ($modx->getCollection('modTemplateVarResource',$c))

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

15 Posts

rwam_de Reply #7, 12 years, 11 months ago

Sorry, this didn't work for me. I've got the following error:

Unknown column 'modTemplateVarResource.CONCAT('||', value, '||')' in 'where clause'

I extend my solution with templating:

<?php
$id = $modx->getOption('id', $scriptProperties, $modx->resource->get('id'));
$tpl = $modx->getOption('tpl', $scriptProperties);
$tv_id = $modx->getOption('tv_id', $scriptProperties, 9);

$query = $modx->newQuery('modResource');
$query->innerJoin('modTemplateVarResource', 'TemplateVarResources');
$query->where(array(
    array(
        'TemplateVarResources.tmplvarid=' . $tv_id,
        'CONCAT(\'||\', TemplateVarResources.value, \'||\') LIKE \'%||' . $id . '||%\''
    )
));

$ids = array();
$collection = $modx->getCollection('modResource', $query);

$output = '';

foreach ($collection as $object) {
    $resId = $object->get('id');
    $ids[] = $resId;
    if (!empty($tpl)) {
        $output .= $modx->getChunk($tpl, array(
            'id' => $resId,
            'menutitle' => $object->get('menutitle'),
            'pagetitle' => $object->get('pagetitle')
        ));
    }
}

if (empty($tpl)) {
    return implode(',', $ids);
} else {
    return $output;
}

here is my chunk "linkedResources":

<li><a href="[[~[[+id]]]]">[[+menutitle:isempty=`[[+pagetitle]]`]]</a></li>

and here is the snippet call:

<ul>
  [[getRelatedProducts? &id=`[[+id]]` &tpl=`linkedResources`]]
</ul>

And there is a link list.