Wow, thank you sir! I will definitely take a deep look at this.
I did come up with a workaround that's not ideal (it basically ignores data
where the data says the row is its own parent).
I added a conditions: ["table.id != parent.id".lit] to the rcte_tree
options.
Incidentally, having the ability to specify a composite key and primary key
was a major factor for me getting this working with a large table. Thank
you for all the work you put into this!
On Tuesday, June 9, 2020 at 5:10:16 PM UTC-4, Jeremy Evans wrote:
>
> On Tuesday, June 9, 2020 at 12:35:20 PM UTC-7, Jeremy Evans wrote:
>>
>> On Tuesday, June 9, 2020 at 11:04:30 AM UTC-7, Jason Landry wrote:
>>>
>>> I recently starting using this plugin and is absolutely fantastic for my
>>> needs. I mean seriously incredibly awesome.
>>>
>>> I do have an issue that I am trying to work around -- not an issue with
>>> the code, but with the data we feed in.
>>>
>>> In a few cases, we have some "legacy" data that has the child id =
>>> parent id (we don't use PK's for this, long story, I am working with what
>>> I have :) )
>>>
>>> This of course causes a cyclic redundancy, and it does it right within
>>> the CTE itself.
>>>
>>> I can handle this by excluding the row in my query but I'd like to make
>>> sure it can't happen at all. Is there any way to add a `where` clause to
>>> both selects in CTEs UNION select? I basically never want to include where
>>> the join a parent has the same value as a child.
>>>
>>> The data will get cleaned up, but it does make me worry a bit about
>>> other similar issues.
>>>
>>
>> The rcte_tree plugin currently expects that roots have a NULL parent_id.
>> We could probably support an option that also considered child_id =
>> parent_id the same as NULL parent_id. I'll take a look at the plugin and
>> see how difficult that would be.
>>
>
> After taking a look at the plugin, I don't think the added complexity to
> support this type of broken tree is worth the benefit. Here's a diff to
> get you started until you can get the data fixed. It passes some basic
> tests, but still locks up when running the rcte_tree integration specs.
>
> diff --git a/lib/sequel/plugins/rcte_tree.rb
> b/lib/sequel/plugins/rcte_tree.rb
> index 23919759e..a67b9eb26 100644
> --- a/lib/sequel/plugins/rcte_tree.rb
> +++ b/lib/sequel/plugins/rcte_tree.rb
> @@ -71,6 +71,9 @@ module Sequel
> # (default: :t)
> # :level_alias :: The symbol identifier to use when eagerly loading
> descendants
> # up to a given level (default: :x_level_x)
> + # :parent_is_current :: Consider cases where the foreign key is the
> same as the
> + # the primary key to be the same as the foreign
> key being
> + # NULL.
> module RcteTree
> # Create the appropriate parent, children, ancestors, and
> descendants
> # associations for the model.
> @@ -122,6 +125,19 @@ module Sequel
> parent = opts.merge(opts.fetch(:parent, OPTS)).fetch(:name,
> :parent)
> childrena = opts.merge(opts.fetch(:children, OPTS)).fetch(:name,
> :children)
>
> + model_dataset = if parent_is_current = opts[:parent_is_current]
> + proc do |model|
> + model.
> + dataset.
> + extension(:select_remove).
> + select_remove(*key_array).
> + select_append(*key_array.zip(prkey_array).map{|k, pk|
> Sequel.case({pk=>nil}, k, k).as(k)}).
> + from_self(:alias=>model.table_name)
> + end
> + else
> + proc{|model| model.dataset}
> + end
> +
> opts[:reciprocal] = nil
> a = opts.merge(opts.fetch(:ancestors, OPTS))
> ancestors = a.fetch(:name, :ancestors)
> @@ -131,8 +147,12 @@ module Sequel
> end
> a[:eager_loader_key] = key
> a[:dataset] ||= proc do
> - base_ds = model.where(prkey_array.zip(key_array.map{|k|
> get_column_value(k)}))
> - recursive_ds = model.join(t, key_array.zip(prkey_array))
> + model_ds = model_dataset.call(model)
> + base_ds = model_ds.where(prkey_array.zip(key_array.map{|k|
> get_column_value(k)}))
> + if parent_is_current
> + base_ds = base_ds.exclude(prkey_array.zip(prkey_array.map{|k|
> get_column_value(k)}))
> + end
> + recursive_ds = model_ds.join(t, key_array.zip(prkey_array))
> if c = a[:conditions]
> (base_ds, recursive_ds) = [base_ds, recursive_ds].map do |ds|
> (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ?
> ds.where(*c) : ds.where(c)
> @@ -170,16 +190,24 @@ module Sequel
> id_map = eo[:id_map]
> parent_map = {}
> children_map = {}
> + skip_map = {} if parent_is_current
> eo[:rows].each do |obj|
> - parent_map[prkey_conv[obj]] = obj
> - (children_map[key_conv[obj]] ||= []) << obj
> obj.associations[ancestors] = []
> obj.associations[parent] = nil
> + if parent_is_current
> + if prkey_array.zip(key_array).all?{|pk, k|
> obj.get_column_value(k) == obj.get_column_value(pk)}
> + skip_map[prkey_conv[obj]] = true
> + next
> + end
> + end
> + parent_map[prkey_conv[obj]] = obj
> + (children_map[key_conv[obj]] ||= []) << obj
> end
> r = model.association_reflection(ancestors)
> - base_case = model.where(prkey=>id_map.keys).
> + model_ds = model_dataset.call(model)
> + base_case = model_ds.where(prkey=>id_map.keys).
> select(*ancestor_base_case_columns)
> - recursive_case = model.join(t, key_array.zip(prkey_array)).
> + recursive_case = model_ds.join(t, key_array.zip(prkey_array)).
> select(*recursive_case_columns)
> if c = r[:conditions]
> (base_case, recursive_case) = [base_case, recursive_case].map
> do |ds|
> @@ -205,6 +233,10 @@ module Sequel
> (children_map[key_conv[obj]] ||= []) << obj
> end
>
> + if skip_map && skip_map[opk]
> + next
> + end
> +
> if roots = id_map[extract_key_alias[obj]]
> roots.each do |root|
> root.associations[ancestors] << obj
> @@ -229,8 +261,9 @@ module Sequel
> end
> la = d[:level_alias] ||= :x_level_x
> d[:dataset] ||= proc do
> - base_ds = model.where(key_array.zip(prkey_array.map{|k|
> get_column_value(k)}))
> - recursive_ds = model.join(t, prkey_array.zip(key_array))
> + model_ds = model_dataset.call(model)
> + base_ds = model_ds.where(key_array.zip(prkey_array.map{|k|
> get_column_value(k)}))
> + recursive_ds = model_ds.join(t, prkey_array.zip(key_array))
> if c = d[:conditions]
> (base_ds, recursive_ds) = [base_ds, recursive_ds].map do |ds|
> (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ?
> ds.where(*c) : ds.where(c)
> @@ -278,9 +311,10 @@ module Sequel
> obj.associations[childrena] = []
> end
> r = model.association_reflection(descendants)
> - base_case = model.where(key=>id_map.keys).
> + model_ds = model_dataset.call(model)
> + base_case = model_ds.where(key=>id_map.keys).
> select(*descendant_base_case_columns)
> - recursive_case = model.join(t, prkey_array.zip(key_array)).
> + recursive_case = model_ds.join(t, prkey_array.zip(key_array)).
> select(*recursive_case_columns)
> if c = r[:conditions]
> (base_case, recursive_case) = [base_case, recursive_case].map
> do |ds|
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/3bb122b2-f04b-4a3c-a1bd-8808ea947921o%40googlegroups.com.
