.joins issue

[davidrenne]

I am finding that somehow when joins is used joining another table against a pipeline function, an incorrect alias is used in the joins.

It is using the entire package.function name in the INNER JOIN instead of the alias you automatically generate in the pipelined_function_alias.

Example:

SELECT "MP".* FROM TABLE(MY_PACKAGE.MY_PROCEDURE(:parameter)) "MP" INNER JOIN "XXXXXXXX" ON "XXXXXXXX"."XXXXXXXX" = "MY_PACKAGE"."MY_PROCEDURE"."ID"

This instead should be "MP" instead of the full object name.

I am about to abandon this gem because queries are not working as planned when doing more complex things. Things such as the find method are not working properly, but if I find_by_id they work because the where function is called I believe.

I dont know if I have time to learn all of the code and how low level active record works to get these queries to work properly.

This might be back to the drawing board to get existing models and code to play nice with a pipeline function I think unless I can solve this joins issue and maybe the .find() issue and the decimal issue I have encountered.

I was really hoping this gem could be my savior and that all existing queries could be easily redirected with reads going to the new models and existing find, sorts, joins etc etc would just work because the record structure is the same results coming from the pipeline function that simply filters out data dynamically based on session/rules/parameters.

;.-(

[davidrenne]

It would at least be cool if you could try reproducing. Basically create a new model that you could in theory call joins(:new_table) with Users pipeline function call and ensure this is a regular model that is not a rails-plsql model and see if the alias issue is repeatable.

[flash-gordon]

Consider that in most cases using pipelined functions for filtering is not such effective as using plain views. Pipeline functions are more about using imperative logic for building datasets. That's why in my project I never had to join function result set with table/view.
Anyway, what version of Rails are you using?

[davidrenne]

Ok might try just putting it in a view.  3.2.15 is the version I am using.

Thanks!
—
Dave

On Mon, Oct 27, 2014 at 5:16 PM, Nikita Shilnikov
[email protected] wrote:

Consider that in most cases using pipelined functions for filtering is not such affective as using plain views. Pipeline functions are more about using imperative logic for building datasets. That's why in my project I never had to join function result set with table/view.

Anyway, what version of Rails are you using?

Reply to this email directly or view it on GitHub:
#5 (comment)

[davidrenne]

I really have no clue how we could even be using .joins(:modelSymbol) anyways. The whole idea of things like that is that the sql is just build using AREL. But since pipeline functions typically need a parameter passed, rails-plsql probably should not even support this. Since these all need to be bound for parameters on the pipeline functions. Maybe I need to use a raw SQL join of some kind and bind in the parameters that way inside the SQL.

I think I am going to try to join things via raw SQL and subselects.