flexible search relacion many to many

[Former Member]

Hy everybody. I want to create a flexible search query to find all products for one vendor.

I have this code:

 "select {" + ProductModel.PK + "} FROM " +
             "{" + ProductModel._TYPECODE + " as p join productvendorrelation as pv on {p" + ProductModel.PK + "} = {pv.source} join " + VendorModel._TYPECODE + "as v on {pv.target} = {v"+ VendorModel.PK +"}}" +
             "where {v.pk} = ?vendorPk ";

The problem I have is that "productvendorrelation" is a many to many relation and I don't know how to access it if I don't have typecode.

The relation is:

 <relation code="ProductVendorRelation" autocreate="true"
             generate="false" localized="false">
             <deployment table="ProductVendorRel" typecode="2010" />
             <sourceElement type="Product" cardinality="many"
                 navigable="false" />
             <targetElement type="Vendor" qualifier="vendors"
                 collectiontype="set" cardinality="many" ordered="true">
                 <modifiers read="true" write="true" search="true"
                     optional="true" />
             </targetElement>
         </relation>

Thanx a lot.

[arvind-kumar_avinash]

The problem I have is that "productvendorrelation" is a many to many relation and I don't know how to access it if I don't have typecode.

Your requirement is not clear. Can you please clarify your requirement with some example/pseudo-code?

[Former Member]

hy

What I mean is, to access product table for example I use ProductModel._TYPECODE.

But I need to have a join with table "productvendorrelation" but I dont have porductvendorrelationModel and in productmodel I have no attribute to relation those tables.

[arvind-kumar_avinash · Accepted solution]

You can retrieve all the information from these tables based on the relation e.g. there is a many-to-many relation between Product and Category as shown below:

 <relation code="CategoryProductRelation" autocreate="true" generate="true" localized="false">
     <deployment table="Cat2ProdRel" typecode="143"/>
     <sourceElement qualifier="supercategories" type="Category" cardinality="many" ordered="false">
         <description>Super Categories</description>
         <modifiers read="true" write="true" search="true" optional="true"/>
     </sourceElement>
     <targetElement qualifier="products" type="Product" cardinality="many" collectiontype="list" ordered="true">
         <description>Products</description>
         <modifiers read="true" write="true" search="true" optional="true"/>
     </targetElement>        
 </relation>

and you can access whatever information you want using their relationship e.g.

 SELECT {p:PK}, {c:code} FROM
 {
    Product as p JOIN CategoryProductRelation as rel
    ON {p:PK} = {rel:target}
    JOIN Category AS c
    ON {rel:source} = {c:PK}
 }

What else do you want to achieve?

I think you are confused with ProductModel_TYPECODE. Please note that ProductModel_TYPECODE= "Product" i.e. instead of writing ProductModel_TYPECODE you can simply write Product.

Please let me know if you have further doubt.

[Former Member]

thanx. I was trying to use use the join with the ._TYPECODE of the relationes table but I can use just the name of the table as in the example and it works perfectly ;)

[arvind-kumar_avinash]

You are most welcome.