Active Query Builder support area

Defining automatic joins

Avatar
  • updated
  • Completed

When I connect to a MS Access database with some defined relations, Active Query Builder creates automatic joins - this works fine.

But how can I define automatic joins in combination with a MySQL database?

Example: The primary key "id" of table "users" should be automatically joined with the field "user_id" of the table "emails" (1:n)...

Is it possible to pre-define these relations programmatically?


Thanks in advance!

Avatar
Andrey Zavyalov, PM

Hello, Chris.

If foreign keys are defined in your MySQL database, setting the right syntax provider (MySQLSyntaxProvider) will let Active Query Builder load them automatically. If they aren't defined, you can define them programmatically or using the Metadata Container editor tool: download it (for VCL and Java editions, for .NET edition), run, and load metadata from your database. There you'll be able to create realtions between database objects and save the content to XML file. After that you shouldn't load metadata from your database, but from that XML file.

I can't point you to the right article in our knowledge base describing this functionality because you didn't tell me about what edition of Active Query Builder you are asking.

Avatar
chris

Thanks a lot - it works fine using XML metadata.


One more question:

Is it possible to predefine some conditions in the metadata? (e.g. always append "WHERE deleted = 0" to my SQL-result)


Avatar
Andrey Zavyalov, PM

Hello, Chris.

Please create new threads for new questions. This way you'll probably get answers faster.

You can add conditions to your queries programmatically in several ways. The first way is to modify the query by API right in the QueryBuilder. This way the user will see this change in the Visual Query Builder. This way is illustrated in the Query Modification demo project.

The second way is to use the QueryTransformer API. Changes made to the Query Transformer aren't shown to end-users, as the source query is not changed. Yo'll be able to get the changed SQL text via the QueryTransformer.SQL property. Read more about the Query Transformer in this article: How to change sorting, add filters, limits and aggregations to the query?