Searching for relationships by themselves

behindicebergs

Hey, this is a small feature request that I tried to work out through search_indexing.conf but that I don't think can happen at all. Would it be possible to add an option to search for and list all instances of relationships in primary tables regardless of what they are related to? For example, listing all the times an object is related to something else through a specific relationship type. Right now as far as I can tell you can only look for a relationship to a specific term, entity, etc, but not decide what type of relationship to want to search for.

Maybe this is a silly niche case or can be achieved through some mean I haven't figured out, if so disregard.

darrigan

Hi,
In Providence part? Yes you can!
Look this screen capture, which is an example of my Find > Objects > Browse:

I've added "Entity relationship types", "Place relationship types", "Occurrence relationship types" and "Term relationship types".
Then, when I choose for example "contains the voice of", it returns objects that contains the voice of someone (video or audio objects).

To add this function, you must edit the app/conf/browse.conf (or app/conf/local/browse.conf). In the part

ca_objects = {
	facets = {
...
	}
}

you must add, for example, this:


		rel_entity_facet = {
			type = authority,
			table = ca_relationship_types,
			relative_to = ca_objects_x_entities,
			relationship_table = ca_objects_x_entities,
			group_mode = none,
			label_singular = _("Entity relationship type"),
			label_plural = _("Entity relationship types")
		},

Here is another example for the relationship types with places:

		rel_place_facet = {
			type = authority,
			table = ca_relationship_types,
			relative_to = ca_objects_x_places,
			relationship_table = ca_objects_x_places,
			group_mode = none,
			label_singular = _("Place relationship type"),
			label_plural = _("Place  relationship types")
		},

You can use the same trick for browsing Places, Entities, Occurrences, Storage locations...
I hope this will help you.

behindicebergs

darrigan Darrigan! You absolute king, what a joy to read your reply. I'll try this out during the week and let you know how it goes. Thanks for the tip, I guess I was looking in the wrong config file, sometimes it's forest for the trees. Also, is your name a reference to the game "Mystery of the druids"?

Cheers!

darrigan

Darrigan is my real last name! I spent a lot of time asking questions on this forum when I started. It's only natural that I help others in turn, when I have the ability.

brucek

Can you help with this one:
Browsing Entity-Entity Relationships?
When I try this:
rel_entity_entity_facet = {
type = authority,
table = ca_relationship_types,
relative_to = ca_entities_x_entities,
relationship_table = ca_entities_x_entities,
group_mode = none,
label_singular = _("Entity to Entity relationship type"),
label_plural = _("Entity to Entity relationship types")
},
I get the correct relationships to browse, but when I select an entity, it blows up with
Unknown column 'ca_entities_x_entities.entity_id' in 'on clause'

Any ideas?
Thanks

behindicebergs

brucek

Hey brucek! If you want to find entity x entity relationships you might have to edit the search_indexing.conf file, which is the one that allows you to search self relationships, meaning relationships between the same table. I don't think you'd be able to search specific relationship types unless you made a specific chunk of code for every type of relationship you'd wanna search for, but it's the closest I can mention I know for what you asked. The manual reference is this one:

https://manual.collectiveaccess.org/providence/user/configuration/mainConfiguration/search_indexing.html?

I hope this helps! Let us know how it goes

-Cern

darrigan

Hi Brucek,
I found how to browse entities by entities:

entity_facet = {
			type = authority,
			table = ca_entities,
			relationship_table = ca_entities_x_entities,
			restrict_to_types = [],
			restrict_to_relationship_types = [],
			group_mode = alphabetical,
			label_singular = _("entity"),
			label_plural = _("entities")
		},

But not how to browse entities by relationship types with entities... I've same result as you. It's because in a "self relationship" table such as ca_entities_x_entities, there is no entity_id column, but entity_left_id and entity_right_id. So it can find the relationship types in the table and display them, but the controller is not able to return the good id.

brucek

There is something else going on - I am able to browse and see all the entity-entity relationships - I just can't select an individual relationship type. It tries to go through the motion, but the sql is scrambled:

db error 1054: Unknown column 'ca_entities_x_entities.entity_id' in 'on clause' vs_sql
SELECT ca_entities.entity_id
FROM ca_entities
INNER JOIN ca_entities_x_entities ON ca_entities_x_entities.entity_id = ca_entities.entity_left_id
INNER JOIN ca_relationship_types ON ca_entities_x_entities.type_id = ca_relationship_types.type_id

										WHERE
											(ca_relationship_types.hier_left >= 301 AND ca_relationship_types.hier_right <= 401 AND ca_relationship_types.hier_type_id = 123)

My complete hack to prove this is what is going on was in stick within /lib/db/mysqli.php function execute(){

$vs_sql = str_replace("ca_entities_x_entities.entity_id","ca_entities_x_entities.entity_left_id",$vs_sql);
$vs_sql = str_replace("ca_entities.entity_left_id","ca_entities.entity_id",$vs_sql);

I just can't figure out where the sql is being generated to troubleshoot if it is a bad conf file or an actual bug.

behindicebergs

brucek Welp, I'm completely out of my depth there, sorry. Maybe you can post this error as a separate topic and someone more skilled in SQL than me can help!

behindicebergs

I'm thinking: does that error happen on Browse or Search? On whatever page it's happening you miht want to comment the code out, it may have something to do with what darrigan said before, there is no "ca_entities_x_entities.entity_id" column, which seems to still be in the query for your error (the syntax shows "INNER JOIN ca_entities_x_entities ON ca_entities_x_entities.entity_id = ca_entities.entity_left_id").

darrigan

Hi Brucek,
Try this:

SELECT ca_entities.entity_id
FROM ca_entities
INNER JOIN ca_entities_x_entities ON ca_entities_x_entities.entity_left_id = ca_entities.entity_id
INNER JOIN ca_relationship_types ON ca_entities_x_entities.type_id = ca_relationship_types.type_id

SELECT ca_entities.entity_id
FROM ca_entities
INNER JOIN ca_entities_x_entities ON ca_entities_x_entities.entity_right_id = ca_entities.entity_id
INNER JOIN ca_relationship_types ON ca_entities_x_entities.type_id = ca_relationship_types.type_id

brucek

The problem is I DONT KNOW WHERE/HOW this SQL is generated.
I suspect it is within /lib/browse/BrowseEngine.php function execute() but I just have not been able to hash out how & where.

I know what the SQL should be...just not WHERE to fix it.