Hi

I have a construction of documents (documentversion) and tags, where tags on documents are maintained through in intermediate table:

DocumentVersion - TagsOnVersion - Tags

In the LLBLGen model, I have the 1-many and many-1 relations, imported during the database-first wizard, and I have then manually added the many-to-many relationship with navigtors, so that DocumentVersions now has TagCollectionViaTagsonversion and Tags has DocumentversionCollectionViaTagsonversion.

When I then fetch data for DocumentVersions and want to include Tags, I create this query:

                var qf = new QueryFactory();
                var q = qf.Documentversion
                    .From(QueryTarget
                        .LeftJoin(DocumentversionEntity.Relations.DocumentEntityUsingDocumentId) // not related to this question
                        .LeftJoin(DocumentversionEntity.Relations.TagsonversionEntityUsingDocumentVersionId)
                        .LeftJoin(TagsonversionEntity.Relations.TagEntityUsingTagId)
                        )
                    .WithPath(DocumentversionEntity.PrefetchPathDocument)
                    .WithPath(DocumentversionEntity.PrefetchPathTagsonversions
                        .WithSubPath(TagsonversionEntity.PrefetchPathTag));

and subsequently I try to show the relevant Tags for a DocumentVersion like this:

                var tagsString = string.Join(",", documentVersion.TagCollectionViaTagsonversion.Select(t => t.Value));

However, the TagsCollectionViaTagsonversion always contains 0 elements.

Looking at the DocumentVersion entity, from the .Tagsonversions navigator, I get a collection with the expected 2 elements (or how many tags are defined on a given document), so data seem to be correct as such - but the many-to-many relation is not populated, or I access it in a wrong manner. Do I need to do anything else to query/prefetch the many-to-many related data?

From the documentation (https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Tutorials%20and%20examples/examples_howdoi.htm#how-do-i-add-an-entity-a-to-an-entity-bs-collection-of-as-if-a-and-b-have-an-mn-relation-) I understand that the many-to-many relationship is read-only, and that new entries must be added to relevant tables. As such, I would expect adding an existing tag to an existing document would be a matter of adding a TagsonversionsEntity. Is that correct?

How about deleting a Tag? Would I manually need to delete all related Tagsonversions entries, or can a cascading delete be used?

I find the documentation on the many-to-many relationships somewhat.. ahem... "limited" - well, maybe not the documentation per se, but at least examples would be great for learning how to work with it. Can you point to a resource of that?

Any help would be greatly appreciated

/Jan

LLBLGen 5.8 (5.8.0) RTM Adapter template Mysql 5.6 Devart dotconnect for MySQL express 8.16.1541.0

Hi Jan,

When you add a M:N relationship, also a navigator is added. The M:N navigator is also another prefetchPath you can use in your prefetch graph. So you don't need to add the intermediate entities, just the M:N one. You also don't have to add the relationships, as those are automatically added when fetching the prefetch paths. You add relationships when you filter on related entities. For prefetchPaths it's not necessary. Something like this:

var qf = new QueryFactory();
var q = qf.Documentversion   
    .WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

//...
var tagsString = string.Join(",", documentVersion.TagCollectionViaTagsonversion.Select(t => t.Value));

See this documentation link. Hope that helps

daelmo wrote:

... Something like this:

var qf = new QueryFactory();
var q = qf.Documentversion   
    .WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

//...
var tagsString = string.Join(",", documentVersion.TagCollectionViaTagsonversion.Select(t => t.Value));

Thank you. I've been manually adding both relations and prefetchpaths - I didn't realise I could do it without the relations. Thats just great So, now I see why the navigator contained 0 elements - I didn't add the prefetch path. The reason being that VS must have failed to pick up generated code, as the prefetch path for the M:N relation was not available in intellisense (I looked for it) - but there were other "anomalies" as well. Now it works, and is even simpler than before, so thanks a lot!

Hi again

So, now my problem is that I need to filter on the "far end" of the m:n relationship. I'm building a search query where one can search for terms in the title of a documentversion, and for documentversions with a specific tag.

What I conceptually want is something like "get all documentversions where TagsToSearchFor contains at least one of the documentversion tags".

Get all documents with tags "orange" and "green" would return DocA (this is what I'm looking for) Get all documents with tags "orange" or "green" would return DocA and DocB

SQL to retrieve a documentversion that is tagged with tags with id 1, 11 and 5 can be done at least like this:

Joining multiple times

SELECT *
FROM documentversions dv
INNER JOIN tagsonversions tov
ON tov.`DocumentVersionId` = dv.`Id` AND tov.`TagId` = 1 
INNER JOIN tagsonversions tov2
ON tov2.`DocumentVersionId` = dv.`Id` AND tov2.`TagId` = 11
INNER JOIN tagsonversions tov3
ON tov3.`DocumentVersionId` = dv.`Id` AND tov3.`TagId` = 5
;

or starting with the tagsonversions table, finding documentversions where the count of elements found matches the number of searched-for-tags, here "3"

SELECT *
FROM documentversions dv
WHERE dv.id IN (
   SELECT DocumentVersionId FROM 
   tagsonversions tov
   WHERE tov.`TagId` IN (1,11,5)
   GROUP BY DocumentVersionId
    HAVING COUNT(*) = 3
)

Both seem clumsy, and I'm sure LLBLGen is able to do this in a much more efficient way. How do I query for that?

I am also trying to retrieve the "number of usages" for tags. The SQL being

SELECT tags.*,
    COUNT(tagsonversions.DocumentVersionId) as Usages 
FROM tags
LEFT JOIN tagsonversions
    ON tagsonversions.TagId = tags.Id
GROUP BY tags.id

which in the above example would return the collection of tags and the count of their usages: id1 - orange - 1 id2 - green - 2 id3 - blue - 1 id4 - red - 1 how can that be retrieved?

Hoping for help - I have a hard time understanding the m:n parts of the documentation.

What I conceptually want is something like "get all documentversions where TagsToSearchFor contains at least one of the documentversion tags"

Since you want to filter on a related entity, then now you need to add the relation to the related entity and use a predicate. This has nothing to do with the fact that you also want to fetch the related entities, hence the PrefecthPath.

var qf = new QueryFactory();
var q = qf.Documentversion   
.From(QueryTarget
                        .LeftJoin(DocumentversionEntity.Relations.TagsonversionEntityUsingDocumentVersionId)
                        .Where(TagsonversionFields.TagId.In(values))
    .WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

I am also trying to retrieve the "number of usages" for tags. The SQL being

This code example in the documentation is a good example.