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/450c3000-7db9-482f-bce9-fd6834d3dfc7o%40googlegroups.com.
