• How can I extract data from two tabke#

  • 36931
    92

    pierrot1010 Reply #1, 3 months, 3 weeks ago

    Reply
    Dear All,

    I am not a developper with MODx but I need to select to table
    modx_memeber_group
    modx_document_group

    I protected some ressource and some user can access it because the are in Group resource.
    I also created a snipest wich display some child resource.
    I found how to filter, hidden document, but I need to filter document which are protetec.

    In other words
    1) If no user is authentified, the protected documetn are not showed
    2) If user is logged, only the documents which belong to the resource group that user is owned

    I know that the information are in the two emntionned table and I know how to get the user id.

    But now, I do not know how to make the mysql query with MODx. I could make the usual mysql request, but I whish to respect the MODx syntax and then I would appreciate if you could inform me, how to do it?

    Many thank for your help



  • 36931
    92

    pierrot1010 Reply #2, 3 months, 3 weeks ago

    Reply
    I tried somethink like this, but of course it does not work
    $user->get('id');
  //[[+PREFIX]]
  	$dc = $modx->db->query("SELECT mg.user_group, mg.member, dg.document_group, dg.document FROM modx_member_groups mg, modx_document_groups dg WHERE mg.user_group = dg.document_group AND mg.member = ".$user->get('id'));

    This is evo or revo?


  • 36931
    92

    pierrot1010 Reply #3, 3 months, 2 weeks ago

    Reply
    (I am using MOdx 2.2.0 (revo))
    I think I close to the solution, but I am sorry, this is still not clear yet.
    // Box is a table?
$c = $xpdo->newQuery('Box');
// BowOwner is the second table to join?
$c->innerJoin('BoxOwner','Owner');
$c->where(array(
   'width' => 5,
   'Owner.user' => 2,
));
$c->sortby('name','ASC');
$c->limit(5);
// What's Box here
$boxes = $xpdo->getCollection('Box',$c);

    I am a bit confused.

    By the way, I found that solution:
    // Extartait id de l'user
		$user = $modx->getObject('modUser', array('username'=>$logUser));	
  		$user->get('id');
	  	
	  	// Extrait les documents protégés mais dont il est autorisé 	  
	  	$sql = "SELECT mg.user_group, mg.member, dg.document_group, dg.document FROM modx_member_groups mg, modx_document_groups dg WHERE mg.user_group = dg.document_group AND mg.member = {$user->get('id')}";
  		
	  	if ($stmt = $modx->prepare($sql) AND $stmt->execute() AND $result = $stmt->fetchAll(PDO::FETCH_ASSOC)){
		
		  	foreach($result as $key => $val){
			
			  	array_push($docProtectedShow,$val['document']);
			  	// $docProtected = id Document protégé et autorisé pour l'user
	  		
			}
	 	
		}

    It works, but I would like to code 100% Modx, then to use newQuery
    May I ask you and help to have the equilavent with newQuery()?

    I have another question
    I found this : $obj = $modx->getObject('modDocument', array('id'=>$id))
    to get data for resource.
    // Stock dans $obj les infos sur le document enfant
			$obj = $modx->getObject('modDocument', array('id'=>$id));
		  
		  	// Si le document est publié, le menu pas caché, pas effacé, retient le et continue 
		  	if($obj->get('published') == 1 AND $obj->get('hidemenu') == 0 AND $obj->get('delete') == 0){


    In my code I noticed that if we are not loggin, $obj = $modx->getObject('modDocument', array('id'=>$id)); can not retrieve resource data on protected resource
    (http://rtfm.modx.com/display/revolution20/Making+Member-Only+Pages)

    With $obj = $modx->getObject(), is there an easy way to "say":
    If resources are protected => do nothing
    if there are not => retrive info

    Many thanks for your help, I creating my first snipest ad I learn a lot )