ERROR: 42703: column "project" does not exist

dpetrovz · November 17, 2022, 2:32pm

If the project module is turned off, the contact is displayed normally. If you enable the project module, an error occurs

ksar · November 17, 2022, 8:17pm

Hello,

What type of SQL server are you using ?
I’m assuming it’s PostgreSQL?

If yes can you create a bug on Issues · Dolibarr/dolibarr · GitHub ?

Thanks

dpetrovz · November 17, 2022, 8:35pm

Hi!
PostgreSQL 13x is used on FreeBSD 13.2
So far the issue has been resolved by adding the project and external columns (char) to llx_c_type_contact

Best regards,
-D. Petrov

ksar · November 17, 2022, 8:54pm

Hello,

In fact the problem is just because of " instead of ’
on lines :

github.com

Dolibarr/dolibarr/blob/develop/htdocs/core/lib/contact.lib.php#L74


      
          		$cachekey = 'count_projects_contact_'.$object->id;
          		$dataretrieved = dol_getcache($cachekey);
          
          
		if (!is_null($dataretrieved)) {
          			$nbProject = $dataretrieved;
          		} else {
          			$sql = 'SELECT COUNT(n.rowid) as nb';
          			$sql .= ' FROM '.MAIN_DB_PREFIX.'projet as n';
          			$sql .= ' INNER JOIN '.MAIN_DB_PREFIX.'element_contact as cc ON (n.rowid = cc.element_id)';
          			$sql .= ' WHERE cc.fk_socpeople = '.((int) $object->id);
          			$sql .= ' AND cc.fk_c_type_contact IN (SELECT rowid FROM '.MAIN_DB_PREFIX.'c_type_contact WHERE element="project" AND source="external")';
          			$sql .= ' AND n.entity IN ('.getEntity('project').')';
          			$resql = $db->query($sql);
          			if ($resql) {
          				$obj = $db->fetch_object($resql);
          				$nbProject = $obj->nb;
          			} else {
          				dol_print_error($db);
          			}
          			dol_setcache($cachekey, $nbProject, 120);	// If setting cache fails, this is not a problem, so we do not test result.
          		}

github.com

Dolibarr/dolibarr/blob/develop/htdocs/core/lib/contact.lib.php#L195


      
          		print '<div class="div-table-responsive">';
          		print "\n".'<table class="noborder" width=100%>';
          
          
		$sql  = 'SELECT p.rowid as id, p.entity, p.title, p.ref, p.public, p.dateo as do, p.datee as de, p.fk_statut as status, p.fk_opp_status, p.opp_amount, p.opp_percent, p.tms as date_update, p.budget_amount';
          		$sql .= ', cls.code as opp_status_code, ctc.libelle';
          		$sql .= ' FROM '.MAIN_DB_PREFIX.'projet as p';
          		$sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'c_lead_status as cls on p.fk_opp_status = cls.rowid';
          		$sql .= ' INNER JOIN '.MAIN_DB_PREFIX.'element_contact as cc ON (p.rowid = cc.element_id)';
          		$sql .= ' INNER JOIN '.MAIN_DB_PREFIX.'c_type_contact as ctc ON (ctc.rowid = cc.fk_c_type_contact)';
          		$sql .= ' WHERE cc.fk_socpeople = '.((int) $object->id);
          		$sql .= ' AND ctc.element="project" AND ctc.source="external"';
          		$sql .= ' AND p.entity IN ('.getEntity('project').')';
          		$sql .= ' ORDER BY p.dateo DESC';
          
          
		$result = $db->query($sql);
          		if ($result) {
          			$num = $db->num_rows($result);
          
          
			print '<tr class="liste_titre">';
          			print '<td>'.$langs->trans("Ref").'</td>';
          			print '<td>'.$langs->trans("Name").'</td>';

I will push a modification