5 Posts
jmm
6 years ago
Topic

Hello guys,

I have created a form that contains a list of attendees to events.

The list of attendees is a dynamic selection list based on Joomla users.

I would like to make a search request to get all the events I have attended to.

I have made a search based on my id, but if I use the permissive, I have events I have not attented to (my id is 51, and I have event for userid 151, 251, 1517, ...)

If I use the strict I have only, because, there is only one event for which I was alone.

Can you tell me how to set the search to get the exact match on one id of the list

Here is how it store in DB : 

::cr__user::1272,51,1824,1630,1688,1820::/cr__user::

::cr__user::1517,1345,1607,1361,1343,1455,1773,1713,1756::/cr__user::

I want want to retreive the first line, but not the second

Thanks for any help or advice

JMM

Get a VIP membership
1283 Posts
Bucklash
6 years ago
3
Level 1

Hi

It's not clear to me how you have data stored in db.... what table? What field?Have you tried 'any word exact'

Do you have multiple records stored similar to field_x?

I found you have to 'map' data when relating content to another content. I did a custom table where I mapped say userA to event1, and userA to event134 etc, one row per entry.

I can share an example if that sounds like the way. Otherwise others might have better method.

5 Posts
jmm
6 years ago
2
Level 2

It is stored in a table called "jom25_cck_store_form_compte_rendu".

The where clause does not consider the ids are separated with comas

if I try 'any exact word' here is the where clause : AND ((t1.cr__user REGEXP "(::cr__user::)51(::/cr__user::)"))

This can not work unless my id is the only id for the event.

The strange thing is that when I display the content, the ids are converted back in a list of name.

I use a 'Select - Dynamic' field type to store the ids of the attendees.

1283 Posts
Bucklash
6 years ago
1
Level 3

Yeah, I think that is something you will struggle with.

Below is code I use to map data so can searxh better and easier

// BeforeRender
// Get values currently stored in form
// Add to JSession
$cckMap['start']['lesson_files'] = explode(',', $fields['choose_lesson_files']->value);
$cckMap['start']['external_link'] = explode(',', $fields['choose_external_link']->value); 
$cckMap['start']['11th_fret'] = explode(',', $fields['choose_11th_frets']->value);
$cckMap['start']['skills_concepts'] = explode(',', $fields['choose_skills_concepts']->value);
$cckMap['start']['bands_artists'] = explode(',', $fields['choose_bands_and_artists']->value);


$session = JFactory::getSession();
$session->set('cck_map', $cckMap);<br>
// AfterStore
// Update table with new values or delete from table if no longer required

// Get a handle to the Joomla! application object
$application = JFactory::getApplication();
$session = JFactory::getSession();
$cckMap = $session->get('cck_map'); // the array of initial values


// Make end strings in to array
// e.g. $cckMap['end']['blog_types'] = array;
$cckMap['end']['lesson_files'] = explode(',', $fields['choose_lesson_files']->value);
$cckMap['end']['external_link'] = explode(',', $fields['choose_external_link']->value); 
$cckMap['end']['11th_fret'] = explode(',', $fields['choose_11th_frets']->value);
$cckMap['end']['skills_concepts'] = explode(',', $fields['choose_skills_concepts']->value);
$cckMap['end']['bands_artists'] = explode(',', $fields['choose_bands_and_artists']->value);



// cck type i.e. some_content_type
$cckMap['cck'] = $fields['cck_value']-> live_value;



// CLEAN ARRAYS (remove duplicates)
foreach ($cckMap['start'] as $k => $v) 
{
	$cckMap['start'][$k] = array_filter($v);
}
foreach ($cckMap['end'] as $k => $v) 
{
	$cckMap['end'][$k] = array_filter($v);
}


// test if like to
// foreach($cckMap['start']  as $k => $v)
// {
// 	foreach($v as $k2 => $v2)
// 	{
// 	JFactory::getApplication()->enqueueMessage($k2.' = '.$v2, 'message');
// 	}
// }


// foreach($cckMap['end']  as $k => $v)
// {
// 	foreach($v as $k2 => $v2)
// 	{
// 		JFactory::getApplication()->enqueueMessage($k2.' = '.$v2, 'message');
// 	}
// }



// if deletion or creation required, Get a db connection.
$db = JFactory::getDbo();


// DELETE MAPS
foreach ($cckMap['end'] as $k => $v) 
{			
	// $v = array()
	foreach ($cckMap['start'][$k] as $k2 => $v2) 
	{


		// if start value is not in end array....
		if (!in_array($v2, $v))
		{
			$db->clear($query);
			$query = $db->getQuery(true);
			
			$conditions = array(
			    $db->quoteName('pk') . ' = ' . $config['pk'],
			    $db->quoteName('cck') . '  = ' . $db->quote($cckMap['cck']),
			    $db->quoteName('fk') . ' = ' . $v2,
			    $db->quoteName('fk_cck') . ' = ' . $db->quote($k)
			);


			$query->delete($db->quoteName('#__cck_map'));
			$query->where($conditions);


			$db->setQuery($query);
			$deleted = $db->execute();


		}
	}
}




// CREATE MAPS
foreach ($cckMap['start'] as $k => $v) 
{
	// $v = array()
	
	foreach ($cckMap['end'][$k] as $k2 => $v2) 
	{


		// if end value is not in start array....
		if (!in_array($v2, $v))
		{


			// Create and populate an object.
			$cckMapAdd = new stdClass();


			$cckMapAdd->pk = (int) $config['pk'];
			$cckMapAdd->cck = $cckMap['cck'];
			$cckMapAdd->fk = (int) $v2;
			$cckMapAdd->fk_cck = $k;


			// Insert the object into the #__cck_map table.
			$cckMapAdded = JFactory::getDbo()->insertObject('#__cck_map', $cckMapAdd);


		}
	}
}
<br>

I store this in a free table, though you could do this with a Seblod Content Type ie create a content type based on Article Object and create/delete that way.

Then need to do Search Join on your List and Search types to get results required.

5 Posts
jmm
6 years ago
0
Level 4

Thanks for your answer, but can you tell me in which file I should this code?

On another han, I was wondering why the search close does not contain the separator to find an exact word in a list with sonething like this

AND ((t1.cr__user like"(::cr__user::)%51,") or ((t1.cr__user like"%,51,%") or ((t1.cr__user like"%,51(::/cr__user::)"))

as my separator is the comma

1283 Posts
Bucklash
6 years ago
1
Level 1

Hi

You can put it in the beforerender and afterstore or beforestore fields. 

Re search... maybe do feature request on github I suppose.

5 Posts
jmm
6 years ago
0
Level 2

I'll try your suggestion, but I am not sure I know enough to do it right

On the ohter hand, I have found a fix to a malformed sql query for "Each exact" mode.

The "OR" are missing

I am going to report the bug and submit my fix

5 Posts
jmm
6 years ago
0
Level 1

After discussing with dev team, it appears it is a bug.

It will fixed in next version.

There was also bug in queries I don't use, it will be fixed too.

Get a Book for SEBLOD