Value list constraints

[gorzek]

I have an application that consists of Projects, which contain Submissions. Projects and Submissions have a one-to-many relationship. On each Submission, however, there is a field in which the previous Submission must be specified (for ordering purposes). What I want is a value list that returns ONLY the Submissions for that Project. As it works now, the value list returns every Submission.

The value list definition looks like this:

Code: Select all

[previous]
__sql__ = "SELECT id,title FROM submissions ORDER BY previous"


What I would want to have is a "WHERE project_id=?" clause that automatically constrains the value list to Submissions under the current Project.

Is there a way to do this without modifying the system, or will I need to write a delegate or something else?

Thanks in advance!

[shannah]

This is a bit of a tricky issue as there are a few ways to accomplish this and all have their pitfalls.

The main strategies to consider are:
1. Use javascript in the form to filter the options of your 'previous' select list depending on which project is selected.

2. Create a dynamic valuelist in the delegate class.

The javascript method is probably the most flexible as you can make it work on new record forms as well as existing record forms.

With the dynamic valuelist method you must be careful because valuelists are used in to display the value of a record elsewhere in the application (other than the forms) and you must make sure that (for example in list view) that all possible values are contained in the valuelist - not just for one project - but in the edit action you only return a subset of those values.

I'd recommend the javascript approach. You can check out some of the documentation on customizing the xataface look and feel for some information on how to embed content into certain "blocks" and "slots" of the xataface interface. If you have questions about this approach, feel free to post further.

Best regards

Steve

[gorzek]

Thank you for the quick reply. I will take a look at the JS approach, since it sounds to be the most flexible and least adverse.

[gorzek]

Now that I think about it, JS would actually be a really bad idea for this application. The submissions table will eventually be large--perhaps tens of thousands of records, or more. Filtering down to a handful via JS seems very expensive.

I think I will make a custom value list. For each user, there will be specific projects (and thus submissions) they will have access to. I would want distinct lists based on the action, in any case: users would be able to view any project and its submissions, but would only be able to edit ones they belonged to or owned (and there is a table that defines that association). So, I'll have to think on this some more. If you have any pointers, I'd love to hear them.

[shannah]

Ok. This is one place where you'll need to be a little creative then. It may not be a good idea to use a valuelist at all for this since a valuelist is loaded on each page request where the valuelist is used (all values in the valuelist). If there are tens of thousands of values then this could be problematic for performance.

The best strategy (I would say) is to create a calculated field for the value in your table (for details, list, find, etc..), and then make a custom widget for the edit form to edit the id.

E.g.

In the fields.ini file:

Code: Select all

__sql__ = "select t1.*, c.name as category_name from mytable t1 left join categories c on t1.category_id = c.id"


Then hide the category_id field form the list and details view:

Code: Select all

[category_id]
    visibility:list=hidden
    visibility:browse=hidden


This will effectively replace the category_id field in the list and details view with your category_name field to show the name of the category rather than the id.

The hard part is now allowing the user to edit the category id. This is best done with javascript. You can use JSON to do this quite easily as Xataface provides an action named export_json which exports the found set as in JSON format.

E.g. if the categories table has fields (id,name, projectID) we could obtain a JSON array of all categories with projectID=10 by entering the URL:
index.php?-table=categories&-action=export_json&projectID=10

You can then use AJAX to extend your javascript strategy to update your category_id select list to only load the category ids for the current project.

e.g.

Code: Select all

function block__category_id_widget(){
    echo <<<END
    <script language="javascript"><!--
        require(DATAFACE_URL+'/js/ajaxgold.js');
            // Import ajax gold library for ajax functions
        function projectChanged(select){
            var selectedProjectID = select.options[select.selectedIndex].value;
            var url = DATAFACE_SITE_HREF+'?-table=categories&-action=export_json&projectID='+selectedProjectID;
            getDataReturnText(url, function(text){
                var categoryIDSelect = document.getElementById('category_id');
                categoryIDSelect.options.length = 0;
                    // clear the select list.
                eval('var options='+text+';'));
                    // Get all the categories from the JSON return
                for ( var i=0; i<options.length; i++){
                    categoryIDSelect.options[categoryIDSelect.options.length] = new Option(options[i].name, options[i].id);
                }
            });
        }
        var projectIDWidget = document.getElementById('project_id');
        var projectID.onchange=projectChanged;
        //--></script>
        <select name="category_id" id="category_id"></select>
END;

}



Or something along these lines. This example needs refining to show the correct currently selected category id and possibly some other things (it may also contain errors as I haven't tested it --- but I have done the same thing before so the strategy works.

-Steve

[gorzek]

I'm going to have to take some time to absorb this. I suppose I mostly wanted to know if there was a built-in solution for this. Looks like there isn't, so I will "get creative," as you said.

I am still very happy with Xataface. I looked at several PHP frameworks before settling on it. It is very extensible, which I like.

Thanks for your help, and patience!

[shannah]

I would like to automate this feature in future versions, but for now, you'll have to do some custom coding.

-Steve

[gorzek]

OK, I am making headway on my custom value list, but there is one thing I am having trouble with: obtaining a reference to the parent record when on a new, related record.

Essentially, I want to know the ID of the project a new submission is going to belong to, and be able to use that in my delegate class for building custom value lists. I tried getParent(), but it seems not to be returning anything--perhaps because it's a new record and thus doesn't exist yet?

This is when attempting to add a submission using the "add new submissions record" relationship action within a project.

[gorzek]

Apparently, I like to make things harder than they are.

I ended up doing this for my custom select widget:

Code: Select all

   function block__previous_widget(&$record) {
      $app =& Dataface_Application::getInstance();
      $record =& $app->getRecord(); // get the parent record
      $pid = $record->val('id');
      $sql = "SELECT id,title from submissions WHERE projects_id=" . $pid . " ORDER BY previous;";
      $res = mysql_query($sql, df_db());

      echo "<select>";
      echo "<option>(First submission)</option>\n";
      while($row = mysql_fetch_array($res, MYSQL_ASSOC))
      {
         echo "<option>" . $row['title'] . "</option>\n";
      }
      echo "</select>";
   }


Thanks for your help!

[kevinwen]

I used this feature and it looks like the strategy works, but finally got a bug related to the query. I define 'DATAFACE_DEBUG_DB' in the app so I can see the query performed:

when I pass the url below to the AJAX call with countries table that has a complex query defined in __sql__ in fields.ini,

Code: Select all

var url = DATAFACE_SITE_HREF+'?-table=countries&-action=export_json&country_id=56';

the where clause of the query performed is:
WHERE `countries`.`country_id` = '56'

However, when I pass the url below to AJAX call with different table (ref_table that join the countries table, for example) that defines a complex __sql__,

Code: Select all

var url = DATAFACE_SITE_HREF+'?-table=ref_table&-action=export_json&country_id=56';

the where clause looks like this:
WHERE `country_id` LIKE CONCAT('%','56','%')'
So the result contain a record with country_id = 156. This is not what I want.

Is this consider a bug?

[shannah]

Can you briefly describe:
1. What you think should happen
2. What does happen.

[kevinwen]

I did know what happened. Xataface treats single equal '=' as partial match and double equal '==' as exact matching.

The first URL below doesn't work for exact matching, but the 2nd one does:

Code: Select all

var url = DATAFACE_SITE_HREF+'?-table=countries&-action=export_json&country_id=56';
var url = DATAFACE_SITE_HREF+'?-table=countries&-action=export_json&country_id==56';


This is a great framework. Thanks, Steve.

[kevinwen]

Steve,

I did found some issue for the permission. I defined the permission function like this:

Code: Select all

    function subcategory_id__permissions(&$record){
        $auth =& Dataface_AuthenticationTool::getInstance();
        $user =& $auth->getLoggedInUser();
        //If the user is null then nobody is logged in... no access. This will force a login prompt.
        if (!isset($user)) return Dataface_PermissionsTool::NO_ACCESS();
        $role = $user->val('user_role');
        if ($role == "ADMIN") return null;
        if ($role == "MANAGER") return null;
        $user_type = $user->val('user_type');
        if ($user_type == "T") return null;
        if ($user_type == "E") return array('edit'=>0);
    }


So if the logged in user type is "E" then we won't allow it to edit the record. However, when I defined the widget function for subcategory_id field, the permission is ignored:

Code: Select all

    function block__subcategory_id_widget(){
       $app =& Dataface_Application::getInstance();
        $record = $app->getRecord();
        $query =& $app->getQuery();

echo <<<END

            var categoryWidget = document.getElementById('category_id');
            function categoryChanged(){
                var selected_category = categoryWidget.options[categoryWidget.selectedIndex].value;
                var subcategoryIDSelect = document.getElementById('subcategory_id');   
                subcategoryIDSelect.options.length = 0;
                subcategoryIDSelect.options[subcategoryIDSelect.options.length] = new Option("Please Select ...", "");
               
           var url = DATAFACE_SITE_HREF+'?-table=subcategories&-action=export_json&-limit=9999&category_id='+selected_category;
                getDataReturnText(url, function(text){
                    eval('var options='+text+';');
                    for ( var i=0; i<options.length; i++){
                        subcategoryIDSelect.options[subcategoryIDSelect.options.length] = new Option(options[i].subcategory_name, options[i].subcategory_id);
                        if (page_load_on_subcategory === true && options[i].subcategory_id == pre_selected_subcategory){
                            page_load_on_subcategory = false;
                            subcategoryIDSelect.selectedIndex = i+1;
                        }
                    }
                });
                subcategoryChanged();
            }
            categoryWidget.onchange = categoryChanged;
           
        //--></script>
END;
    }


The "E" type user gets the hard-coded select box for subcategory field in the widget function, while it would get the static subcateogry name before this widget function was added. Do you know how I can solve this problem? Thanks.

[shannah]

Currently, if you override a block, it is up to you to check the permissions of the record. E.g.:

Code: Select all

if ( $record->checkPermission('edit', array('field'=>'myfield')) ){
    //  Show your custom widget
} else {
    return PEAR::raiseError("Use the default widget since the user doesn't have edit permissions");
}