DB with multiple Models: SqlQuery just 1 model?

[derekwallace]

Hi,
On our MySQL DB we have multiple Models (root node packages).

I want to do a query and am only intereseted in data from a specific model.

Currently when it runs it returns informaotin from all Models.
org.sparx.Repository oRepos = new org.sparx.Repository()
oRepos.OpenFile(sReposName);

// simple Query
String sSqlQuery  = "SELECT t_object.ea_guid AS GUID  FROM t_object";
String sTmp = oRepos.SQLQuery(sSqlQuery);
System.out.println(sTmp);


The t_object table has objects that are in ALL models. Is there a way to filter the query so it only returns items that are in one of the root node models?

Thx
Derek

[Geert Bellekens]

In the EA Search there's the #Branch# macro that can be used, but I don't think there's something like that when dealing with the SQLQuery operation.

Three options:
- Create a query with enough joins to reach the top and test that package

Code: [Select]

           string sqlGetElements = @" SELECT o.Object_ID FROM t_object as o
                                    left join t_package p on o.Package_ID = p.Package_ID
                                    left join t_package p2 on p.Parent_ID = p2.Package_ID
                                    left join t_package p3 on p2.Parent_ID = p3.Package_ID
                                    left join t_package p4 on p3.Parent_ID = p4.Package_ID
                                    left join t_package p5 on p4.Parent_ID = p5.Package_ID
                                    left join t_package p6 on p5.Parent_ID = p6.Package_ID
                                    left join t_package p7 on p6.Parent_ID = p7.Package_ID
                                    left join t_package p8 on p7.Parent_ID = p8.Package_ID
                                    left join t_package p9 on p8.Parent_ID = p9.Package_ID

                                    where
                                    p.package_ID = " + startingPackageID + @"
                                        or p2.package_ID = " + startingPackageID + @"
                                        or p3.package_ID = " + startingPackageID + @"
                                        or p4.package_ID = " + startingPackageID + @"
                                        or p5.package_ID = " + startingPackageID + @"
                                        or p6.package_ID = " + startingPackageID + @"
                                        or p7.package_ID = " + startingPackageID + @"
                                        or p8.package_ID = " + startingPackageID + @"
                                        or p9.package_ID = " + startingPackageID;- Build a list (in code) of all your packageID's of the model you need and then use that in the query (where t_object.package_ID in (....))
- Get all objects and then filter them by recursing up the package tree (in code)

Each solution is suitable in a different circumstance.

Geert

[qwerty]

I'm offline at the moment, but there's a #-macro in the SQL search which returns only objects inside a certain package. You would need to create a custom SQL search and perform a search on that. But probably Geert's solution is more straight forward...

q.

[derekwallace]

Thx Geert. I used the SQL query with JOIN and it worked perfectly.

For others .
I used the GetModelId method to get the ID of the varous root node models. The returned integer is what i used as the startingPackageId

Thx
Derek