How to display One-to-many Relationships in ListViews

👨‍💻 Development Help
1

I have a set of custom modules set in place and deal with the following scenario:

A [Deal] always consists of an optional set of [Companies] that are involved as “Bidders” in a Deal. [Companies] is an own module that entails the ID and some additional information on a company.

I want to display all the involved Bidders (there are at most 3 at a time) in one line for each Deal in the according ListView

I already managed to do the same in a DetailView by implementing a custom field and filling it with s that includes the Companies’ names and ids all concatenated into one line.
Now, I want to do the same in the ListView for each given record.

However, this, to my mind, is a different story in ListViews since you cannot really access the entire set of cells of a column manually.

I already know how to implement a custom column (there’s always the conditional formatting example given) and use LogicHooks but I would really like to refrain from firing a sql query for each record separately.

Thanks in advance for any piece of advice.

2

if you don’t want to use a hook that gets called in a list view, firing an sql for each row, you can workaround this by update that single item each time that relationship is updated, and that update could be a single text field with the names concatenated.

for example

Item A1 has no relation to any B
When you add a relation between A1 and B1 you write that value B1 in a text field in A Module
When you add a relation between A1 and B2 you write and concatenate B1. B2

And you need to handle the deleted relations and stuff, maybe check all the relations between module A and B and just overwrite instead of adding and removing on each relationship change

Best regards

1 Like
3

Thanks for the reply!

that would be one solution but that would include manipulating the data base tables which i would also like to circumvent…

I was thinking to actually to the same in the listView as I did in the detail view but it seems that I cant access the bean right away in a similar vein:

  • Access the bean
  • manipulate the according field in which the textfield with concatenated company names (optimally with links to their detail views) should be stored
  • display it on the list view

would that actually be a proper solution in listViews? From my understanding that does not work, does it?

4

I think if you can do something in one type of hook it is possible in another one. Can you share what you did in DetailView here?

best regards

5

I just implemented it in fashion of a Logic Hook. It does work. However I can imagine having to deal with performance issues when there’s more data stored.

This is the way I handled it in the Detail View (edited some terms to make the example a bit more easier to digest)
Some additional info: I am looking for bidders in which the displayed company was the target of a deal.

view.detail.php


function getBidders(){
	$bidder_ids = array();
	$sql = <<<EOF
	SELECT `companies`.`id` as "id", bidder.`name` as "bidder_name", `companies`.`name` as "name", `companies_deal_c`.`companies_dealdeal_idb` as "deal_id", `companies_deal_1_c`.`companies_deal_1companies_ida` as "bidder_id" 
	FROM `companies` 
	LEFT JOIN `companies_deal_c` ON `companies_deal_c`.`companies_dealcompanies_ida` = `companies`.`id` 
	LEFT JOIN `companies_deal_1_c` ON `companies_deal_c`.`companies_dealdeal_idb` = `companies_deal_1_c`.`companies_deal_1deal_idb` 
	LEFT JOIN (SELECT id, name FROM `companies`) as bidder ON `bidder`.id = `companies_deal_1_c`.`companies_deal_1companies_ida` 
	WHERE `companies`.`id` = '%s' 
	AND `companies_deal_1_c`.`deleted` = 0 
EOF;
	$result = $GLOBALS['db']->query(sprintf($sql, $this->bean->id));
	while($row = $GLOBALS['db']->fetchByAssoc($result)){
		array_push($bidder_ids,(object)array('bidder_id' => $row['bidder_id'], 'bidder_name' => $row['bidder_name']));
	}
	return $bidder_ids;
}
	
function preDisplay(){		
	$bidder_ids = $this->getBidders();
	$bidders = array();
	if($bidder_ids){
		foreach ($bidder_ids as $bidder) {
			array_push($bidders,"<a href = 'index.php?action=DetailView&module=Companies&record=".$bidder->bidder_id."'>".$bidder->bidder_name."</a>");
		}
	}
	$this->bean->bidder_non_db = join(", ", $bidders);
}

resulting vardefs.ext.php excerpt


..,
$dictionary["Companies"]["fields"]["bidder_non_db"] = array (
    'name' => 'bidder_non_db',
    'type' => 'varchar',
    'default'=> '',
    'vname' => 'LBL_BIDDER',
    'reportable' =>true,
    'source' => 'non-db'
);
...
1 Like