getRulesFromSQL() : Auto detect field IDs by field names using the already defined config.

[gordon-matt]

As per the last comment on issue #452 , I would like to ask for getRulesFromSQL() to be modified to determine the correct IDs based on the Field Names. At first, this was only going to be a request for convenience sake, but I have just discovered an issue where this is actually very important (at least to me anyway) and will probably need to involve the SQL parser being updated as well:

I have successfully been able to do a joined query by basically setting the field names in my config with a prefix.. Example:

filters: [
	{ id: 'booking-id', field: '"B"."Id"', label: 'Booking: ID' },
	{ id: 'booking-code', field: '"B"."Code"', label: 'Booking: Code' },
	{ id: 'booking-date', field: '"B"."BookingDate"', label: 'Booking: Date' },
	{ id: 'booking-destination', field: '"B"."Destination"', label: 'Booking: Destination' },
	//etc
	{ id: 'customer-id', field: '"C"."Id"', label: 'Customer: ID' },
	{ id: 'customer-email', field: '"C"."Email"', label: 'Customer: Email' },
	//etc
]

Then, in the backend, I simply create my SQL query as follows:

SELECT *
FROM "Bookings" AS "B"
INNER JOIN "Customers" "C" ON "C"."Id" = "B"."CustomerId"
WHERE <Query From QueryBuilder Goes Here>

It works beautifully. But then when I want to load a saved query, it doesn't work because this is obviously not supported in the SQL parser. I would like to suggest that QueryBuilder already has the field definitions, so in theory it shouldn't be too hard to simply match each field name with an ID automatically. For example, "C"."Id" would be matched to the ID "customer-id".

What's the possibility of having this done?

[gordon-matt]

Alright I have a workaround and it does the job nicely.. I would still prefer a built-in solution for this scenario, but I can work with this for now. Below is a quick explanation of what I am doing for anyone who may wish to query multiple tables like myself:

1. To query, do as per original comment above.

2. To load a saved SQL query, we need to take a couple of extra steps, as follows:

Firstly, I replace the table alias + dot as follows:

query.Sql = query.Sql.Replace("\"B\".\"", "\"B_");
query.Sql = query.Sql.Replace("\"C\".\"", "\"C_");

Then in the JavaScript, I do the following:

$('#query-builder').on('getSQLFieldID.queryBuilder.filter', function (e) {
	switch (e.value) {
		case "B_Id": e.value = "booking_id"; break;
		case "B_Code": e.value = "booking_code"; break;
		//etc

		case "C_Id": e.value = "customer_id"; break;
		case "C_Email": e.value = "customer_email"; break;
		//etc
		default: break;
	}
});

Quite simple actually.

[mistic100]

It cannot work because you add useless double quotes everywhere. Once parsed "B"."Id" becomes B.Id, even if there were an automatic lookup on the field name, it couldn't match your original quoted string.

I am working the the Abstract Syntax Tree of the SQL query, where I have no way to know that the original field name was quoted.

---

I decided a while ago to not trying to be too smart in order to have consistent behaviour when multiple filters work on the same field.

I realize it's not very common, so I will implement a lookup on the field, I throw an error only if multiple filters with the same field are found.

It will not solve your issue though.

[gordon-matt]

@mistic100 , Thank you for your response. However, the double quotes are not useless. If I don't use the double quotes, then I get error messages like:

column "xxxx" does not exist

For example, if I say BookingsCount, instead of "BookingsCount", then postgres insists on converting the column name to lower case and giving me an error like:

column "bookingscount" does not exist

I'm sure there is probably a setting somewhere to turn off case sensitivity, but I won't always have access to the database in all projects I work on and need to work with what I have. So as I said: the quotes are not useless.

[gordon-matt]

Hi there. It's been 2 years since I opened this request. I am working on a new project that will be using this Query Builder.. and I was just looking at this issue again and noticed that the last thing that happened here was you made a fix, but only for Mongo. Is this fix now applied to SQL as well?

[mistic100]

There are two commits linked to this issue, one being c1c451e