我是DynamoDB的新手.
在我的应用程序中,每个注册用户的帐户都有一些传感器,这些传感器每隔5分钟就会向我们的数据库发送一些数据.
现在我要显示“如果用户已登录他的帐户,我应该为每个传感器显示所有传感器的最新时间戳值”.
我做了三个查询,例如“我在他的帐户下获取了所有设备 – >原始传感器ID – >每个传感器最新值”,因此这个过程需要很长时间才能执行.
我的表看起来像这样:
用户:
---------------------
|sno | userId |
---------------------
| 1 | naveenkumar |
|--------------------
| 2 | abc |
|--------------------
| 3 | xyz |
|--------------------
sensor_devices:
---------------------
| id | sensorId |
---------------------
| 1 | sensor1 |
|--------------------
| 2 | sensor2 |
|--------------------
| 3 | sensor3 |
|--------------------
| 4 | sensor4 |
---------------------
| 5 | sensor5 |
---------------------
和
用户传感器:
----------------------
| userId | sensorId |
----------------------
| 1 | 1 |
|---------------------
| 1 | 2 |
|---------------------
| 1 | 4 |
|---------------------
| 2 | 5 |
|---------------------
data_tbl:
---------------------------------------------
| sensorId | value | timestamp |
---------------------------------------------
| sensor1 | 4.3 | 2014-01-21 11:21:00|
|--------------------------------------------
| sensor2 | 5.0 | 2014-01-21 11:22:00|
|--------------------------------------------
| sensor3 | 10.0 | 2014-01-21 11:19:00|
|--------------------------------------------
| sensor4 | 6.3 | 2014-01-21 11:25:00|
|--------------------------------------------
| sensor1 | 8.3 | 2014-01-21 11:26:00|
|--------------------------------------------
| sensor2 | 6.0 | 2014-01-21 11:27:00|
|--------------------------------------------
| sensor3 | 9.0 | 2014-01-21 11:24:00|
|--------------------------------------------
| sensor4 | 6.3 | 2014-01-21 11:30:00|
|--------------------------------------------
和最终输出表将在他的主页上看到用户:
---------------------------------------------
| sensorId | value | timestamp |
---------------------------------------------
| sensor1 | 8.3 | 2014-01-21 11:26:00|
|--------------------------------------------
| sensor2 | 6.0 | 2014-01-21 11:27:00|
|--------------------------------------------
| sensor4 | 6.3 | 2014-01-21 11:30:00|
|--------------------------------------------
我的查询看起来像这样,并且需要很长时间才能执行,所以请为我提供更好的解决方案:
我们假设$_SESSION [‘login_id’] = 1;
$aws = Aws::factory('config.php');
$client = $aws->get("dynamodb");
$tableName = "data_tbl";
echo "
echo "
";echo "
Sensor ID";echo "
value";echo "
Timestamp";echo "
";//Query to get user devices
$devices = $client->query(array(
"TableName" => "users-sensors",
"KeyConditions" => array(
"userId" => array(
"ComparisonOperator" => ComparisonOperator::EQ,
"AttributeValueList" => array(
array(Type::STRING => $_SESSION['login_id'])
)
)
)
));
foreach ($devices['Items'] AS $key=>$value) {
$id = $value['sensorId']['S'];
//Query to get original sensorId from sensor serial number
$device = $client->query(array(
"TableName" => "sensor_devices",
"KeyConditions" => array(
"id" => array(
"ComparisonOperator" => ComparisonOperator::EQ,
"AttributeValueList" => array(
array(Type::STRING => $id)
)
)
)
));
$dids = $device['Items'][0]['sensorId'][Type::STRING];
//Query to get all latest values of sensors
$response = $client->query(array(
"TableName" => $tableName,
"KeyConditions" => array(
"sensorId" => array(
"ComparisonOperator" => ComparisonOperator::EQ,
"AttributeValueList" => array(
array(Type::STRING => $dids)
)
)
),
"Select" => "SPECIFIC_ATTRIBUTES",
"AttributesToGet" => array("sensorId","value","timestamp"),
"ScanIndexForward" => false,
"Limit" => 1
));
foreach ($response['Items'] AS $key=>$value) {
echo "
";$link = "graph.php?id=".$id;
echo "
".$value['sensorId']['S']."";echo "
".$value['value']['S']."";$epoch = $value['timestamp']['S'];
$timestamp = date('Y-m-d H:i:s', $epoch);
echo "
".$timestamp."";echo "
";}
}
echo "
";?>
如果我能得到答案,我会完成我的工作.
谢谢