f3 framework sql query join

Question

I have the following tables:

tbl_users
===============
uid
username
password
gid

tbl_groups
===============
gid
name
type

I am trying to figure out how to use the sqlmapper in f3 to be able to query both tables where username equals $_POST["username"] and be able to get the group name and type as well. Is it possible to join like queries using this framework with sqlmapper?

I've been searching around and can't find any examples on that.

Answer 1

you can try to setup some virtual fields for this:

$mapper->group_name = 'select name from tbl_groups where tbl_groups.gid=tbl_users.gid';
$mapper->group_type = 'select type from tbl_groups where tbl_groups.gid=tbl_users.gid';
$mapper->load(array('uid = ?',123));

echo $mapper->group_name;

Answer 2

Here is the example with {VIEW}:

I have implemented here pagination with SQL Mapper using View view_user_list_with_referral

    $dropInstantWinnerView = $this->db->exec("DROP VIEW IF EXISTS view_user_list_with_referral;");
    $createInstantWinnerView = $this->db->exec("CREATE VIEW view_user_list_with_referral AS SELECT u.fb_id, fb_link, name, r.referred_by, u.created FROM users u LEFT OUTER JOIN referral r ON u.fb_id=r.joinee ");
    $user = new \DB\SQL\Mapper($this->db,'view_user_list_with_referral');
    $limit = 20;
    $page = Pagination::findCurrentPage();
    $order_condition = F3::get("PARAMS.order_condition");
    $order_class= "";
    if(!empty($order_condition)){
        $cond = explode(":", $order_condition);
        $option = array('order' => $cond[0].' '.$cond[1]);
        if($cond[1]=='ASC'){
            $order_condition = $cond[0].':DESC';
            $order_class = ":DESC";
        }else{
            $order_condition = $cond[0].':ASC';
            $order_class = ":ASC";
        }
    }else{
        $option = array('order' => 'created DESC');
    }

    $subset = $user->paginate($page-1, $limit, null, $option);

    $pages = new Pagination($subset['total'], $limit);
    $pages->setTemplate("admin/pagebrowser.html");

    F3::set('pagebrowser', $pages->serve());
    //echo "<pre>";print_r($subset);exit;
    F3::set('page', $page);
    F3::set('order_condition', $order_condition);
    F3::set('total_found_records', $user->count());

I hope it will save someones time :)