How to make a complicated query between 2 tables

[magicu]

Such as:

select * from blog,author where blog.author_id = author.author_id and author.name like '%xxx%'

[QxOrm admin]

select * from blog,author where blog.author_id = author.author_id and author.name like '%xxx%'

Just write something like this :

Code: Select all

QSqlError daoError = qx::dao::fetch_by_query_with_relation("author", qx_query().where("author.name").like("xxx"), my_blog);

[magicu]

select * from blog,author where blog.author_id = author.author_id and author.name like '%xxx%'

Just write something like this :

Code: Select all

QSqlError daoError = qx::dao::fetch_by_query_with_relation("author", qx_query().where("author.name").like("xxx"), my_blog);

I wrote code like this:

Code: Select all

      qx::QxSqlQuery query;
      query.where("AccountGroup.name").like(groupName)
         .and_("AccountType.name").like(typeName);

      AccountList accountList;
      daoError = qx::dao::fetch_by_query_with_all_relation(query, accountList);

QxOrm generate sql:


SELECT Account.id AS Account_id_0,
Account.user_name AS Account_user_name_0,
Account.password AS Account_password_0,
Account.cookie AS Account_cookie_0,
Account.status AS Account_status_0,
Account.create_date AS Account_create_date_0,
Account.group_id AS Account_group_id_0,
AccountGroup_1.id AS AccountGroup_1_id_0,
AccountGroup_1.name AS AccountGroup_1_name_0,
Account.type_id AS Account_type_id_0,
AccountType_2.id AS AccountType_2_id_0,
AccountType_2.name AS AccountType_2_name_0,
AccountType_2.script AS AccountType_2_script_0,
Mail_3.id AS Mail_3_id_0,
Mail_3.account_id AS Mail_3_account_id_0,
Mail_3.system_id AS Mail_3_system_id_0,
Mail_3.is_replied AS Mail_3_is_replied_0,
Mail_3.is_read AS Mail_3_is_read_0,
Mail_3.has_attachment AS Mail_3_has_attachment_0,
Mail_3.size AS Mail_3_size_0,
Mail_3.sender AS Mail_3_sender_0,
Mail_3.subject AS Mail_3_subject_0,
Mail_3.content AS Mail_3_content_0,
Mail_3.sent_date AS Mail_3_sent_date_0,
Mail_3.received_date AS Mail_3_received_date_0
FROM Account
LEFT OUTER JOIN AccountGroup AccountGroup_1 ON AccountGroup_1.id = Account.group_id
LEFT OUTER JOIN AccountType AccountType_2 ON AccountType_2.id = Account.type_id
LEFT OUTER JOIN Mail Mail_3 ON Mail_3.account_id = Account.id
WHERE AccountGroup.name LIKE :AccountGroup_name_1_0 AND AccountType.name LIKE :AccountType_name_3_0

There is an error occured: "Parameter count mismatch". The correct sql should be:

SELECT Account.id AS Account_id_0,
Account.user_name AS Account_user_name_0,
Account.password AS Account_password_0,
Account.cookie AS Account_cookie_0,
Account.status AS Account_status_0,
Account.create_date AS Account_create_date_0,
Account.group_id AS Account_group_id_0,
AccountGroup_1.id AS AccountGroup_1_id_0,
AccountGroup_1.name AS AccountGroup_1_name_0,
Account.type_id AS Account_type_id_0,
AccountType_2.id AS AccountType_2_id_0,
AccountType_2.name AS AccountType_2_name_0,
AccountType_2.script AS AccountType_2_script_0,
Mail_3.id AS Mail_3_id_0,
Mail_3.account_id AS Mail_3_account_id_0,
Mail_3.system_id AS Mail_3_system_id_0,
Mail_3.is_replied AS Mail_3_is_replied_0,
Mail_3.is_read AS Mail_3_is_read_0,
Mail_3.has_attachment AS Mail_3_has_attachment_0,
Mail_3.size AS Mail_3_size_0,
Mail_3.sender AS Mail_3_sender_0,
Mail_3.subject AS Mail_3_subject_0,
Mail_3.content AS Mail_3_content_0,
Mail_3.sent_date AS Mail_3_sent_date_0,
Mail_3.received_date AS Mail_3_received_date_0
FROM Account
LEFT OUTER JOIN AccountGroup AccountGroup_1 ON AccountGroup_1.id = Account.group_id
LEFT OUTER JOIN AccountType AccountType_2 ON AccountType_2.id = Account.type_id
LEFT OUTER JOIN Mail Mail_3 ON Mail_3.account_id = Account.id
WHERE AccountGroup_1.name LIKE :AccountType_2.name AND AccountType_2_name_0 LIKE :AccountType_name_3_0

I can write code like this to fix it, but I think it's not a good way,

Code: Select all

      qx::QxSqlQuery query;
      query.where("AccountGroup_1.name").like(groupName)
         .and_("AccountType_2.name").like(typeName);

How about your opinion?

[QxOrm admin]

How about your opinion?

You are right, it needs to be improved
It will be a part of the improvment of relationships for the next version of QxOrm library (1.2.3)...

With current version, it will work with the following code, so you can use it :

Code: Select all

qx_query query = qx_query().where("AccountGroup_1.name").like(groupName).and_("AccountType_2.name").like(typeName);

[magicu]

How about your opinion?

You are right, it needs to be improved
It will be a part of the improvment of relationships for the next version of QxOrm library (1.2.3)...

With current version, it will work with the following code, so you can use it :

Code: Select all

qx_query query = qx_query().where("AccountGroup_1.name").like(groupName).and_("AccountType_2.name").like(typeName);

How long will QxOrm library (1.2.3) be released?

[QxOrm admin]

How long will QxOrm library (1.2.3) be released?

I can't give you a released date because I don't know yet
It can take a lot of time (1, 2 or 3 months, maybe more...) or not.
If I build a BETA version with your problem resolved, I will add a link to this topic...
But since it can work if you use the workaround (even if it's not the better way), so for me it is not a priority.

[QxOrm admin]

Hi,

You can try this BETA version of QxOrm library (1.2.3 BETA 07) :
http://www.qxorm.com/version/QxOrm_1.2.3_BETA_07.zip

There is a fix to replace automatically your table name with the alias into your query...

[magicu]

Hi,

You can try this BETA version of QxOrm library (1.2.3 BETA 07) :
http://www.qxorm.com/version/QxOrm_1.2.3_BETA_07.zip

There is a fix to replace automatically your table name with the alias into your query...

I'll try it, thank you.

BTW, is there any "readme" about this update?

[QxOrm admin]

is there any "readme" about this update?

Nope sorry !
I will modify the file "changes.txt" when the version will be released...
But, with this BETA version, you can test your problem with complicated query...

[alecn2002]

Looks like this issue was not fixed even in QxOrm 1.2.5?

I use QxOrm 1.2.5 with QT 5.0.1, and still see (omitting SELECT blahblah... part, relation defs and WHERE only):

Code: Select all

FROM "Process"
LEFT OUTER JOIN "ProcessType" "ProcessType_1" ON "ProcessType_1"."id" = "Process"."process_type_id"
LEFT OUTER JOIN "Company" "Company_2" ON "Company_2"."id" = "Process"."owner_id"
LEFT OUTER JOIN "Company" "Company_3" ON "Company_3"."id" = "Process"."so_id"
LEFT OUTER JOIN "ProcessObject" "ProcessObject_4" ON "Process"."id" = "ProcessObject_4"."process_id"
LEFT OUTER JOIN "Object" "Object_4" ON "ProcessObject_4"."object_id" = "Object_4"."id"
LEFT OUTER JOIN "Document" "Document_8" ON "Document_8"."object_id" = "Object_4"."id"
LEFT OUTER JOIN "File" "File_12" ON "File_12"."document_id" = "Document_8"."id"
LEFT OUTER JOIN "Document" "Document_13" ON "Document_13"."process_id" = "Process"."id"
LEFT OUTER JOIN "File" "File_17" ON "File_17"."document_id" = "Document_13"."id"
WHERE ProcessType.short_name = :ProcessType_short_name_1_0
AND ObjectType.short_name = :ObjectType_short_name_3_0


This was generated from :

Code: Select all

QSqlError ProcessDAO::read_by_type_and_objtype_with_relations(Process_list& proc, const QString& ptype, const QString& objtype)
{
const QStringList ProcessDAO::relations(
        QStringList()
                    << "process_type_id"
                    << "owner_id"
                    << "so_id"
                    << "Object_list->Document_list->File_list"
                    << "Document_list->File_list");

    qx::QxSqlQuery query;
    query.where("ProcessType.short_name").isEqualTo(ptype)
            .and_("ObjectType.short_name").isEqualTo(objtype);
    return qx::dao::fetch_by_query_with_relation(relations, query, proc);
}


If needed I could provide relations defs, too