alias id in sql select

[Giorgos Tsolakis]

Dear Steve et al,

I am trying to combine two queries in one that should appear as a grid. So inside the relationships.ini I have:

__sql__ = "
SELECT objectID AS id, box
FROM object
WHERE object.objectID LIKE 'A%'
AND object.campaignID = 'LY22'
AND '$type'='object'

UNION ALL
SELECT notable_pottery.notable_potteryID AS id, box
FROM notable_pottery
WHERE notable_pottery.notable_potteryID LIKE 'A%'
AND notable_pottery.campaignID = 'LY22'
AND '$type'='notable_pottery'
"

If I am not mistaken, the problem is that objectID AS id does not work since the same query (without the $type parameters) works on phpMyAdmin. Moreover, when I reduce the query to the following, the table appears as objectID and not id:

__sql__ = "
SELECT objectID AS id, box
FROM object
WHERE object.objectID LIKE 'A%'
AND object.campaignID = 'LY22'
AND '$type'='object'
"

I have also tried wrapping the id in backticks with no result, and I get a white screen when I wrap it inside single quotation marks.

Do you have any ideas on how I can solve this renaming of column issue please?

Thank you!

[Steve Hannah]

SQL queries in relationships go through the SQL parser as it tries to determine the nature of the relationship for the purposes of being able to add/remove related records.  This puts some constraints on the kinds of queries that can be used.  

I would take one of three approaches. 

1. If this list is conceptually a related list (i.e. related to a particular record in the campaigns table, and would like it to show up as a related tab in the campaigns table, then create a view with your compex query, then use that view in the relationship.

2. If you don't really think about this as a relationship, just create a view, and then use that view directly in your app.

3. Use Xataface's support for dynamic tables.  https://shannah.github.io/xataface-manual/#tables_views_queries

Note: When using views and dynamic tables, make sure that they include at least a conceptual primary key that can uniquely reference a row, and you'll need to explicitly specify this primary key in your fields.ini file using Key=PRI. (see https://shannah.github.io/xataface-manual/#tables_views_queries)

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/CAF4gVnC5RxqkxptB2UG128K05ZLKfTvm6dBrmhKcb9fPxRarZA%40mail.gmail.com.

--

Steve Hannah

Web Lite Solutions Corp.